Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

livio218526
Contributor

Outher join and keep table

Hi everyone,

I have a problem with the join.

I would like to execute a join between 3 tables and keep the result table for reporting.

[MAP PRODUCT HFM 2017]:

LOAD

[HFM CODE] AS [HFM CODE-HFMCode],

[DESCRIPTION],

[Plant abbr],

....

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [MAP PRODUCT HFM 2017]);

[MAP MARKET 2017]:

LOAD

[Market Code] AS [Market-Market Code],

[REGION] AS [Plant_Region-REGION],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [MAP MARKET 2017]);

[MAP BRAND 2017]:

LOAD

[Scenario] AS [MAP BRAND 2017.Scenario],

[Format],

[Years],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [MAP BRAND 2017]);

[HFM DB]:

LOAD

[Year],

[Scenario] AS [HFM DB.Scenario],

[Month] AS [HFM DB.Month],

[Brand],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [HFM DB]);

I'd like to execute an outher join:

HFM DB with MAP MARKET 2017 by MARKET CODE

HFM DB with MAP PRODUCT HFM 2017 by HFM CODE

HFM DB with MAP BRAND 2017 by BRAND



At last I'd like to keep the result table from this outher join.



Thanks in advantage,

Livio

1 Solution

Accepted Solutions

Re: Outher join and keep table

Hi

If you have join field in your excel file, then you can use "Join" like below

[HFM DB]:

LOAD

[Year],

[Scenario] AS [HFM DB.Scenario],

[Month] AS [HFM DB.Month],

[Brand],

[Market Code] AS [Market-Market Code],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [HFM DB]);

Join([HFM DB]) // Outer Join with HFM DB table

LOAD

[Market Code] AS [Market-Market Code],

[REGION] AS [Plant_Region-REGION],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [MAP MARKET 2017]);

For above field, Hope that Market Code is available in the both tables.

18 Replies
YoussefBelloum
Esteemed Contributor

Re: Outher join and keep table

do you have :

MARKET CODE on [HFM DB] and [MAP PRODUCT HFM 2017] ?


HFM CODE on [HFM DB] and [PRODUCT HFM 2017] ?


BRAND on [HFM DB] and [MAP BRAND 2017] ?


livio218526
Contributor

Re: Outher join and keep table

Yes.

However, I'd like to tell you that I have only files excel

YoussefBelloum
Esteemed Contributor

Re: Outher join and keep table

No matter the source:

you can make a try on this:

[HFM DB]:

LOAD

FROM TABLE;

OUTER JOIN([HFM DB])

[MAP PRODUCT HFM 2017]:

LOAD

FROM TABLE;

DROP TABLE [MAP PRODUCT HFM 2017];

OUTER JOIN([HFM DB])

[MAP MARKET 2017]:

LOAD

FROM TABLE;

DROP TABLE [MAP MARKET 2017];


OUTER JOIN([HFM DB])

[MAP BRAND 2017]:

LOAD

FROM TABLE;

DROP TABLE [MAP BRAND 2017];

livio218526
Contributor

Re: Outher join and keep table

[HFM DB]:

LOAD

FROM TABLE; --> what is table?? i don't understand




YoussefBelloum
Esteemed Contributor

Re: Outher join and keep table

sorry fo this, TABLE is your source (excel sheet)

Re: Outher join and keep table

Hi

If you have join field in your excel file, then you can use "Join" like below

[HFM DB]:

LOAD

[Year],

[Scenario] AS [HFM DB.Scenario],

[Month] AS [HFM DB.Month],

[Brand],

[Market Code] AS [Market-Market Code],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [HFM DB]);

Join([HFM DB]) // Outer Join with HFM DB table

LOAD

[Market Code] AS [Market-Market Code],

[REGION] AS [Plant_Region-REGION],

...

FROM lib...

(ooxml, embedded labels, header is 1 lines, table is [MAP MARKET 2017]);

For above field, Hope that Market Code is available in the both tables.

livio218526
Contributor

Re: Outher join and keep table

i have this like source:

[lib://GM Analyzer/CNH - GM Analyzer - Input.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [HFM DB]);

YoussefBelloum
Esteemed Contributor

Re: Outher join and keep table

Yes, like what you wrote in the beginning !

i just remove it to just write the steps to follow

omarbensalem
Esteemed Contributor

Re: Outher join and keep table

Hi Youssef,

Wy are u dropping the tables?