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.
Saleskit:
Load *,
SalesYear & '|' & SalesMonth & '|' & SalesWeek & '|' & SalesLATAMLaraBPCode & '|' & SalesDirection as YMWBPD_Key;
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)",
Segmentation,
Category,
"Month" as SalesMonth,
"Week" as SalesWeek
FROM [lib://External source files/saleskit.qvd]
(qvd);
Database:
Load *,
DataYear & '|' & DataMonth & '|' & DataWeek & '|' & DataLATAMLaraBPCode & '|' & DataDirection as YMWBPD_Key;
LOAD
Service,
Voyage,
Vessel,
Booking,
BL,
"Year" as DataYear,
"Month" as DataMonth,
"Week" as DataWeek,
"LATAM Lara BP code" as DataLATAMLaraBPcode,
Direction as DataDirection
FROM [lib://External source files/export.qvd]
(qvd);
// Then build link table
LinkTable:
Load Distinct // from SalesKit
YMWBPD_Key,
SalesYear as Year,
SalesMonth as Month,
SalesWeek as Week,
SalesLATAMLaraBPCode as [LATAM Lara BP code],
SalesDirection as Direction,
'SalesKit' as FromTable
Resident SalesKit;
Concatenate(LinkTable)
Load Distinct // from Database
YMWBPD_Key,
DataYear as Year,
DataMonth as Month,
DataWeek as Week,
DataLATAMLaraBPCode as [LATAM Lara BP code],
DataDirection as Direction,
'Database' as FromTable
Resident Database;
// Drop un-needed fields
Drop fields DataYear,DataMonth , DataWeek , DataLATAMLaraBPCode , DataDirection,
SalesYear , SalesMonth , SalesWeek , SalesLATAMLaraBPCode , SalesDirection;
