Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
livio218526
Creator
Creator

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
MayilVahanan

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.

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

18 Replies
YoussefBelloum
Champion
Champion

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
Creator
Creator
Author

Yes.

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

YoussefBelloum
Champion
Champion

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
Creator
Creator
Author

[HFM DB]:

LOAD

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




YoussefBelloum
Champion
Champion

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

MayilVahanan

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.

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
livio218526
Creator
Creator
Author

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

Yes, like what you wrote in the beginning !

i just remove it to just write the steps to follow

OmarBenSalem

Hi Youssef,

Wy are u dropping the tables?