join the data from headerfile and data tabs together then concatenate the car, truck and bike data together, while concatenating add a new field called fact source, and name with whatever data you are appending, that will make it easier to identify later on
LOAD ID AS Common_ID,
Data_Code
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is HeaderFile);
left join
LOAD Data_Code,
Type_Data
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Data);
Fact:
LOAD Car_Date AS Common_Key,
Value1,
Car_ID AS Common_ID,
1 as FactType
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Car);
Concatenate(Fact)
LOAD Truck_Date AS Common_Key,
Value2 as Value1,
Zone,
Truck_ID AS Common_ID,
2 as FactType
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Truck);
Concatenate(Fact)
LOAD Bike_Date AS Common_Key,
Value3 as Value1,
Source,
Bike_ID AS Common_ID,
3 as FactType
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Bike);
load * Inline
[FactType,Fact
1,Car
2,Truck
3,Bike];
EXIT Script;
