Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hii,
I need to create a key with 5 fields in comun but i need to keep those fields on both tables when loading.
Saleskit:
LOAD
"Year",
"Sales Country",
"Sales Manager",
"Sales rep",
"Sales Role",
"LATAM Lara BP code",
"Master Customer Name",
"Customer name",
Direction,
"Vol. Budget (Teus)",
Segmentation,
Category,
"Month",
"Week"
FROM [lib://External source files/saleskit.qvd]
(qvd);
Database:
LOAD
Service,
Voyage,
Vessel,
Booking,
BL,
"Year",
"Month",
"Week",
"LATAM Lara BP code",
Direction
FROM [lib://External source files/export.qvd]
(qvd);
They key needs to be with the fiels ("Year"&"Month"&"Week"&"LATAM Lara BP code"&Direction).
The problem is that if i keep all the 5 fields on both table the Qlik will create another synthetic key automaticaly, which i cannot use.
and i cannot not load all the 5 fields from both tables, because i need a "full outer join".
thankss, Karine.
the best way to do this is to create your own link table to host the fields and relate back to original tables with composite keys.
These are the steps:
1. Rename common dimensions to unique names to break associations.
2. Create a composite key in each table with the correct combination of these fields.
3. Build a link table to host the original field values, associated back to the original tables with the composite key.
The best solution is going to depend on how you plan to use the data. Assuming you plan on selecting the same dimension and have it impact both tables
Option 1:
Just concatenate the second table to the first.
Option 2:
If for some reason you do not like Option 1, then you can move all of the common fields to a link table and create a unique id for each table. RowNo() as %KeyName is very reliable.
That would be something like this:
SalesKit:
Load RowNo() as %SalesKitID, * from ....
Database:
Load RowNo() as %SalesDBID, * from ....
LinkTable:
noconcatenate
Load
"Year",
"Month",
"Week",
"LATAM Lara BP code",
Direction,
%SalesKitID
resident SalesKit;
Drop Fields "Year", "Month", "Week", "LATAM Lara BP code" from SalesKit;
Concatenate (LinkTable)
Load
"Year",
"Month",
"Week",
"LATAM Lara BP code",
Direction,
%SalesDBID
Resident Database;
Drop Fields "Year", "Month", "Week", "LATAM Lara BP code" from Database;
the best way to do this is to create your own link table to host the fields and relate back to original tables with composite keys.
These are the steps:
1. Rename common dimensions to unique names to break associations.
2. Create a composite key in each table with the correct combination of these fields.
3. Build a link table to host the original field values, associated back to the original tables with the composite key.
Thank you Lisa!
Amazing! It worked with the Linktables 🙂
Hi Lisa, thank you so much for helping me with this.
I have another problem, and i would love if you could help me again.
So same thing the subject on this forum.
I created the link tables for Saleskit and Database as you suggest and it works well.
but now, i have another table called BudgetAlloc:
BudgetAlloc:
LOAD
"Year" as RMYear,
Cluster,
Country as RMCountry,
SERVICE as RMService,
ZOD as RMZod,
"Reefer flag" as RMreeferflag,
Direction as RMDirection,
"Target/teu/week",
"Month" as RMMonth,
"Week" as RMWeek,
AutoNumber("Year" & '|' & "Month" & '|' & "Week" & '|' & Country & '|' & Direction & '|' & SERVICE & '|' & ZOD & "Reefer flag") as YMWCD_Key
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/BudgetAlloc.qvd]
(qvd);
which the I need to link with the Database:
I can't see any relationship with ZOD and Reefer flag to Database, can you please advise. Also, is there anything that is similar to the DataLATAMLaraBPCode in Budget ?
Hi Lisa, good morning!
Yes, i have those fields on my Database as well.
I don`t have the DataLATAMLaraBPCode in the Budget, that budget i need to connect with my Country, Year, Month, Week, Direction, ZOD, Service and Reefer flag.
We have the following tables:
BudgetAlloc:
LOAD
"Year" as RMYear,
Cluster,
Country as RMCountry,
SERVICE as RMService,
ZOD as RMZod,
"Reefer flag" as RMreeferflag,
Direction as RMDirection,
"Target/teu/week",
"Month" as RMMonth,
"Week" as RMWeek,
AutoNumber("Year" & '|' & "Month" & '|' & "Week" & '|' & Country & '|' & Direction & '|' & SERVICE & '|' & ZOD & "Reefer flag") as YMWCD_Key
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/BudgetAlloc.qvd]
(qvd);
Saleskit:
LOAD
"Year" as SalesYear,
"Sales Country",
"Sales Manager",
"Sales rep",
"Sales Role",
"LATAM Lara BP code" as SalesLATAMLaraBPcode,
"Master Customer Name",
"Customer name",
Direction as SalesDirection,
"Vol. Budget (Teus)",
"VAS Budget ($)",
"CH Budget (Teus)",
"Master BP code",
Country_Code,
"Month" as SalesMonth,
"Week" as SalesWeek,
AutoNumber("Year" & "Month" & "Week" & "LATAM Lara BP code" & Direction ) as Saleskit_Key
FROM [lib://QS_Projects_BA_Miami_RO/30_External source files/ImpChg_1_2023.qvd]
(qvd);
thank you!