Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
MVP
MVP

concatenate 2 sources

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

Re: concatenate 2 sources

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
Contributor II

Re: concatenate 2 sources

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