Issue with loading data into Qlik Sense and avoiding circular references and synthetic keys

Question

I am facing a problem with loading data into Qlik Sense and avoiding circular references and synthetic keys. I have tried the script provided below, but it is not working as expected. I would greatly appreciate it if someone could help me with this issue.

Here is the script I am using:

Cities:
LOAD
    City,
    "City Code",
    Region,
    Latitude,
    Longitude,
    "Desc"
FROM [lib://Esercizio-1/Cities.xlsx]
(ooxml, embedded labels, table is Cities);

Customers:
LOAD
    Customer,
    "Customer Number",
    "City Code"
FROM [lib://Esercizio-1/Customers.xlsx]
(ooxml, embedded labels, table is Customer);


Items:
LOAD
    "Item Number",
    "Product Group",
    "Product Line",
    "Product Sub Group",
    "Product Type"
FROM [lib://Esercizio-1/Item master.xlsx]
(ooxml, embedded labels, table is [Item master]);



Sales:
LOAD
    "%KEY",
    Cost,
    "Customer Number",
    "Date" ,
    
    Date(Year(Date),Month(Date),Day(Date)) as DateKey,
    Day(Date) As Day,
    Month(Date) as Month,
    Month(Date) as MonthNbr,
    Week(Date) as Week,
    Ceil(Month(Date)/3) as Quarter,
    Year(Date) as Year,
    Year(Date) & Month(Date) as YearMonthNbr,
    Year(Date) & '-' & Month(Date) as YearMonth,

    GrossSales,
    "Invoice Date",
    "Invoice Number",
    "Item Desc",
    "Item Number",
    Margin,
    "Order Number",
    "Promised Delivery Date",
    Sales,
    "Sales Qty",
    "Sales Rep Number" as  "Sales Rep ID"
FROM [lib://Esercizio-1/Sales.xlsx]
(ooxml, embedded labels, table is Sales);


Manager:
LOAD
    Manager,
    "Manager Number",
    Path,
    "Sales Rep Name",
    "Sales Rep Name1",
    "Sales Rep Name2",
    "Sales Rep Name3",
    "Sales Rep ID"
FROM [lib://Esercizio-1/Sales rep.csv]
(txt, codepage is 28599, embedded labels, delimiter is ';', msq);

BudgetTable:
LOAD 
     "Product Type",
     Year,
     Region ,
     Budget
FROM [lib://Esercizio-2/Budget.xlsx]
(ooxml, embedded labels, table is Budget);

I would like to know how to avoid circular references and synthetic keys when loading the BudgetTable.

Thank you in advance for your help!

Answer 1

You indeed have some circular references going here:

What you should be deciding here is how handle the mixed granularity that the Budget table introduces. See this fantastic article written by Henric Cronstrom about this exact issue. He has another one here that similarly discusses when to join tables together.

It's not a super easy thing to solve, as there are many data model-related "gotchas," but I think something like this could be a starting point:

Make a compound key

Make a compound key in each table using the table's common fields (the fields shared with any other table). We can use the AutoNumber() function to achieve this easily:

Cities:
Load *
  , AutoNumber( "Region"    & '|' &
                "City Code"
            ) as "_key_cities"
;
Load
    City,
    "City Code",
    Region,
    Latitude,
    Longitude,
    "Desc"
FROM [lib://Esercizio-1/Cities.xlsx]
(ooxml, embedded labels, table is Cities);


Customers:
Load *
  , AutoNumber( "Customer Number"   & '|' &
                "City Code"
            ) as "_key_customers"
;
Load
    Customer,
    "Customer Number",
    "City Code"
FROM [lib://Esercizio-1/Customers.xlsx]
(ooxml, embedded labels, table is Customer);


Items:
Load *
  , AutoNumber( "Item Number"   & '|' &
                "Product Type"
            ) as "_key_items"
;
Load
    "Item Number",
    "Product Group",
    "Product Line",
    "Product Sub Group",
    "Product Type"
FROM [lib://Esercizio-1/Item master.xlsx]
(ooxml, embedded labels, table is "Item master");


Manager:
LOAD
    Manager,
    "Manager Number",
    Path,
    "Sales Rep Name",
    "Sales Rep Name1",
    "Sales Rep Name2",
    "Sales Rep Name3",
    "Sales Rep ID"
FROM [lib://Esercizio-1/Sales rep.csv]
(txt, codepage is 28599, embedded labels, delimiter is ';', msq);


Sales:
Load *
  , AutoNumber( "Year"              & '|' &
                "Item Number"       & '|' &
                "Customer Number"
            ) as "_key_sales"
;
Load
    "%KEY",
    Cost,
    "Customer Number",
    "Date" ,
    
    DateKey,
    Day,
    Month,
    MonthNbr,
    Week,
    Quarter,
    Year,
    YearMonthNbr,
    YearMonth,

    GrossSales,
    "Invoice Date",
    "Invoice Number",
    "Item Desc",
    "Item Number",
    Margin,
    "Order Number",
    "Promised Delivery Date",
    Sales,
    "Sales Qty",
    "Sales Rep ID"
FROM [lib://Esercizio-1/Sales.xlsx]
(ooxml, embedded labels, table is Sales);


BudgetTable:
Load *
  , AutoNumber( "Product Type"  & '|' &
                "Year"          & '|' &
                "Region"
            ) as "_key_budget"
;
Load
     "Product Type",
     Year,
     Region,
     Budget
FROM [lib://Esercizio-2/Budget.xlsx]
(ooxml, embedded labels, table is Budget);

Create a link table

Now we can create a "link" table, which will allow us to link our dimension and fact tables together. We can achieve this by joining the common fields and key fields from each table into this new link table and then dropping the common fields out of the original tables (since they'll now only be needed in one place and will associate back to the original tables):

[Link table]:
Load Distinct 
    [_key_sales]
  , [Year]
  , [Item Number]
  , [Customer Number]
Resident [Sales];

Drop Fields
    [Year]
  , [Item Number]
  , [Customer Number]
From [Sales];


Join ([Link table])
Load Distinct
    [_key_customers]
  , [Customer Number]
  , [City Code]
Resident [Customers];

Drop Fields
    [Customer Number]
  , [City Code]
From [Customers];


Join ([Link table])
Load Distinct
    [_key_items]
  , [Item Number]
  , [Product Type]
Resident [Items];

Drop Fields
    [Item Number]
  , [Product Type]
From [Items];


Join ([Link table])
Load Distinct
    [_key_cities]
  , [Region]
  , [City Code]
Resident [Cities];

Drop Fields
    [Region]
  , [City Code]
From [Cities];


Join ([Link table])
Load Distinct
    [_key_budget]
  , [Product Type]
  , [Year]
  , [Region]
Resident [BudgetTable];

Drop Fields
    [Product Type]
  , [Year]
  , [Region]
From [BudgetTable];

That should give us something like this:

Important notes

It's important to note that my examples above represent a basic example and uses completely random data, since I don't have any of your actual data. So, it will likely be the case that you'll have to tweak a bit of that script to get it working.

One of the decisions you'll need to make is whether or not to simply join your Budget table into the Sales table -- that would be one of the ways you could handle this but may greatly expand the size of that table and make your calculations wonky until you adjust them.