Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

concatenate 2 sources

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!

3 Replies
swuehl
MVP
MVP

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);

Miguel_Angel_Baeyens

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

rajeshqvd
Creator II
Creator II

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);