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

[cepp0]

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!

[BrunPierre]

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);

[cepp0]

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

[BrunPierre]

Are you able to share the files?

[cepp0]

QLIK EXERCISES 

[BrunPierre · Accepted solution]

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');

[cepp0]

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

[BrunPierre]

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.

[cepp0]

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

[BrunPierre]

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