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 🙂