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!

[cepp0]

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