Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 sheets which I want to load and concatenate. The 2 tables are in different xls docs,
both tables have some common datafields, and one unique field : Country and Plant.
Market_Stock:
LOAD Month,
Year,
Country,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period
FROM
C:\Analyses\stocks\market.xlsx
(ooxml, embedded labels, table is db);
Plant_Stock:
LOAD Month,
Year,
Plant,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period
FROM
C:\Analyses\stocks\plant.xlsx
(ooxml, embedded labels, table is db);
How do I concatenate both together, so I have the following result table:
Total_Stock:
Month, Year, Plant, Country, [Product Group], [Quantity /kt], Period
and the empty fields get a null value
Thanks a 1000 times!
Try
Total_Stock:
LOAD Month,
Year,
Country,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period
FROM
C:\Analyses\stocks\market.xlsx
(ooxml, embedded labels, table is db);
Concatenate LOAD Month,
Year,
Plant,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period
FROM
C:\Analyses\stocks\plant.xlsx
(ooxml, embedded labels, table is db);
Hi,
I would join them, provided they share the same format in each field as well as the name:
Total_Stock:
LOAD Month,
Year,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period,
Country
FROM
C:\Analyses\stocks\market.xlsx
(ooxml, embedded labels, table is db);
Plant_Stock: // not needed, but useful for debugging
LEFT JOIN (Total_Stock) LOAD Month,
Year,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period,
Plant
FROM
C:\Analyses\stocks\plant.xlsx
(ooxml, embedded labels, table is db);
Hope that helps.
Miguel
Market_Stock:
LOAD Month,
Year,
Country,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period
FROM
C:\Analyses\stocks\market.xlsx
(ooxml, embedded labels, table is db);
Concatenate
Plant_Stock:
LOAD Month,
Year,
Plant,
[Product Group],
[Quantity /kt],
Date(MakeDate(Year,Month),'MM.YYYY') AS Period
FROM
C:\Analyses\stocks\plant.xlsx
(ooxml, embedded labels, table is db);