topic Re: Issue with loading data into Qlik Sense and avoiding circular references and synthetic keys in Connectivity & Data Prep

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

cepp0 — Tue, 11 Apr 2023 14:48:48 GMT

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!

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

BrunPierre — Wed, 12 Apr 2023 09:16:51 GMT

Just by looking, the best way would be to combine Sales and BudgetTable as they appear to be fact tables using forced concatenation. You could rename the Region field in Cities to Region as "City Region" or comment out with your requirements in mind.

Sales:
LOAD ...

FROM [lib://Esercizio-1/Sales.xlsx]
(ooxml, embedded labels, table is Sales);

Concatenate(Sales)
BudgetTable:
LOAD ...

FROM [lib://Esercizio-2/Budget.xlsx]
(ooxml, embedded labels, table is Budget);

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

cepp0 — Wed, 12 Apr 2023 08:55:27 GMT

It is intended to add a sheet to analyse sales performance against the company's budget, summarised in the file "Budget.xlsx", which contains the estimated sales values (budget) for each product group, for each year and by region.
Objectives of the exercise are:
1) Implement the loading of the budget data with the appropriate changes to the existing data model by evaluating, where necessary, adjustments to the source database
2) Add the "Sales performance vs Budget" sheet in which to implement the display of sales data in comparison with the budget data explorable by
- Year
- Product type
- Region
For the layout, you are free to adopt the data visualisation tools you consider most effective

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

BrunPierre — Wed, 12 Apr 2023 09:17:35 GMT

Are you able to share the files?

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

cepp0 — Wed, 12 Apr 2023 09:20:43 GMT

QLIK EXERCISES

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

BrunPierre — Wed, 12 Apr 2023 12:11:48 GMT

Hope this can help.

///*---------------------------------------------------------------------------------------- // Appending Region to temporary Customer table //----------------------------------------------------------------------------------------*/ Temp: LOAD [Customer Number], [City Code] FROM [..Downloads\Customers.xlsx] (ooxml, embedded labels, table is Customer); Left Join(Temp) LOAD [City Code], Region FROM [..Downloads\Cities.xlsx] (ooxml, embedded labels, table is Cities); ///*---------------------------------------------------------------------------------------- // Temporary Lookup table --- comparision field ([Customer Number])and mapping value field (Region) //----------------------------------------------------------------------------------------*/ RegionMap: Mapping LOAD Distinct [Customer Number], Region Resident Temp; DROP Table Temp; //*---------------------------------------------------------------------------------------- // Temporary Lookup table --- comparision field ([Item Number])and mapping value field ([Product Type]) //----------------------------------------------------------------------------------------*/ ProductTypeMap: Mapping LOAD [Item Number], [Product Type] FROM [..Downloads\Item master.xlsx] (ooxml, embedded labels, table is [Item master]); //*---------------------------------------------------------------------------------------- // Sales and Budget combined //----------------------------------------------------------------------------------------*/ "Sales & Budget": LOAD %KEY, Cost, [Customer Number] as %CustNo, Date(Date) as %Date, // Date, GrossSales, [Invoice Date], [Invoice Number], [Item Desc], [Item Number], Margin, [Order Number], [Promised Delivery Date], Num(Sales,'#,##0.00') as Sales, [Sales Qty], [Sales Rep Number] as %SalesRepID, ApplyMap('ProductTypeMap',[Item Number]) as [Product Type],// Product type populated unto fact table ApplyMap('RegionMap',[Customer Number]) as Region // // Region populated unto fact table FROM [..Downloads\Sales.xlsx] (ooxml, embedded labels, table is Sales); Concatenate("Sales & Budget") Budget: LOAD [Product Type], MakeDate(Year) as %Date, Region, Num(Budget,'#,##0.00') as Budget FROM [..Downloads\Budget.xlsx] (ooxml, embedded labels, table is Budget); //*---------------------------------------------------------------------------------------- // Time period and time attributes //----------------------------------------------------------------------------------------*/ "Master Calendar": Load TempDate AS %Date, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, 'Q' & ceil(month(TempDate) / 3) AS Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay ; //=== Generate a temp table of dates === LOAD date(mindate + IterNo()) AS TempDate ,maxdate // Used in InYearToDate() above, but not kept WHILE mindate + IterNo() <= maxdate; //=== Get min/max dates from Field ===/ LOAD min(FieldValue('%Date', recno()))-1 as mindate, max(FieldValue('%Date', recno())) as maxdate AUTOGENERATE FieldValueCount('%Date');

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

cepp0 — Wed, 12 Apr 2023 13:22:50 GMT

But the Budget field is now empty/null. How is it possible?

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

BrunPierre — Wed, 12 Apr 2023 21:13:13 GMT

On the table viewer it looks sparsely populated, that's the result of concatenating. i.e. adding rows to an existing table.

Having said that, the dataset are still relatable.

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

cepp0 — Thu, 13 Apr 2023 15:15:54 GMT

What If I would like to maintain all fields to obtain same results in the others sheets?

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

BrunPierre — Thu, 13 Apr 2023 21:10:43 GMT

Do you mean the other tables? If yes, you can add it to the model.

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

cepp0 — Fri, 14 Apr 2023 12:38:16 GMT

Ok this is my final solution, thanks a lot for help.

// Applying region to the temporary customer table
Temp:
LOAD [Customer Number], 
[City Code]

FROM [lib://Es1Debug/Customers.xlsx]
(ooxml, embedded labels, table is Customer);

Left Join(Temp)
LOAD [City Code], 
Region
FROM [lib://Es1Debug/Cities.xlsx]
(ooxml, embedded labels, table is Cities);
// Temporary lookup table --- comparison field and mapping value field
RegionMap:
Mapping LOAD Distinct [Customer Number],
Region

Resident Temp;

DROP Table Temp;


// Temporary lookup table --- comparison field and mapping value field
ProductTypeMap:
Mapping LOAD [Item Number], 
[Product Type]

FROM [lib://Es1Debug/Item master.xlsx]
(ooxml, embedded labels, table is [Item master]);

// Sales and budget combinations
"Sales & Budget":
LOAD %KEY, 
Cost, 
[Customer Number], 
Date,
Num(Year(Date),'0000') & Num(Month(Date),'00') & Num(Day(Date),'00') as DateKey,
Day(Date) As Day,
Month(Date) as Month,
Num(Month(Date)) as MonthNbr,
Week(Date) as Week,
'Q' & Ceil(Month(Date)/3) as Quarter,
Year(Date) as Year,
Year(Date) & Num(Month(Date),'00') 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],
ApplyMap('ProductTypeMap',[Item Number]) as [Product Type],// Product Type popolato nella tabella dei fatti
ApplyMap('RegionMap',[Customer Number]) as Region // // Region popolato dalla tabella dei fatti
FROM [lib://Es1Debug/Sales.xlsx]
(ooxml, embedded labels, table is Sales);

Concatenate("Sales & Budget")
Budget:
LOAD [Product Type], 
Year,
Region, //Concatenates with Region in sales, in turn brought in with ApplyMap()
Budget

FROM [lib://Es2Debug/Budget.xlsx]
(ooxml, embedded labels, table is Budget);


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

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

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


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