Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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] ?
Yes.
However, I'd like to tell you that I have only files excel
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];
[HFM DB]:
LOAD
FROM TABLE; --> what is table?? i don't understand
sorry fo this, TABLE is your source (excel sheet)
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.
i have this like source:
[lib://GM Analyzer/CNH - GM Analyzer - Input.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [HFM DB]);
Yes, like what you wrote in the beginning !
i just remove it to just write the steps to follow
Hi Youssef,
Wy are u dropping the tables?