Outher join and keep table

[livio218526]

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

[YoussefBelloum]

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]

Yes.

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

[YoussefBelloum]

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]

[HFM DB]:

LOAD

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

[YoussefBelloum]

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

[MayilVahanan · Accepted solution]

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]

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]

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?