Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
Please help to create a link with PART_NUMBER and Metal Part ID without forming the loop and getting the exact result as show in the screenshot.
Script and Raw Data is enclosed.
Thanks
LK
Hi...
I don't really understand the subject area and therefore can offer 2 different solutions.
1.if the relationship of the tables: DW and CONFIG should be only in the field PART_NUMBER ([Metal Part Id]) regardless of DATA, then I think the solution is only to use Set Analysis
2.
If you assume that the result is the intersection of the keys of all three tables (link_key, report_key, part_number (metal part ID), I think it was right to make a link table.
I slightly changed your script to implement this idea, but did not optimize it. Please compare. I hope it helps.
Because the model has changed for dashboard for TB10 I specified not to display rows with a NULL field for the field EQPID
Script:
CONFIG:
LOAD
MADE ,
[Metal Part ID] AS [Link_PN2],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY AS [Link_LK2]
,[LINK_KEY]&'|'&[Metal Part ID] AS [Link_LK2+PN2]
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
EQPID_Map:
Mapping
LOAD [Eqpt ID],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
RECIPE_Map:
Mapping
LOAD
[Recipe Name],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
GA_TEMP:
LOAD
DATE(REPORT_DATE) AS [Link_RD2],
REPORT_MONTH,
PC_EQP_TYPE,
MOVE_QTY,
RECIPE_NAME,
EQPID,
ApplyMap('RECIPE_Map',RECIPE_NAME,Null() ) AS RECIPE_LinkKey,
ApplyMap('EQPID_Map',EQPID,Null()) AS EQPID_LinkKey
FROM $(vInputPath)
(ooxml, embedded labels, table is DATA)
where REPORT_MONTH =10;
NoConcatenate
GA_PRE:
LOAD *,
if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS [Link_LK1]
,[Link_RD2]&'|'&if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS [Link_RD2+LK1]
RESIDENT GA_TEMP;
DROP TABLE GA_TEMP;
DW:
LOAD
MADE AS MA,
DATE(FLOOR(TRANS_PERIOD)) AS TRANS_PERIOD,
Date(daystart(TRANS_PERIOD, 0, 1 )) AS [Link_RD1],
EVENT_DATE,
PART_NUMBER AS [Link_PN1],
TOTALVALUE
,Date(daystart(TRANS_PERIOD, 0, 1 )) &'|'&[PART_NUMBER] AS [Link_RD1+PN1]
FROM
$(vInputPath)
(ooxml, embedded labels, table is DW);
[LinkTable]:
Load Distinct
[Link_RD1] AS [REPORT_DATE]
,[Link_PN1] AS [PART_NUMBER]
,[Link_RD1] &'|'&[Link_PN1] AS [Link_RD1+PN1]
Resident DW;
Outer Join([LinkTable])
Load Distinct
[Link_RD2] AS [REPORT_DATE]
,[Link_LK1] AS [LINK_KEY]
,[Link_RD2]&'|'&[Link_LK1] AS [Link_RD2+LK1]
Resident GA_PRE;
Outer Join([LinkTable])
Load Distinct
[Link_LK2] AS [LINK_KEY]
,[Link_PN2] AS [PART_NUMBER]
,[Link_PN2] AS [Metal Part ID]
,[Link_LK2]&'|'&[Link_PN2] AS [Link_LK2+PN2]
Resident CONFIG;
Drop Fields [Link_PN1], [Link_RD1], [Link_RD2],[Link_LK1],[Link_LK2],[Link_PN2];
Try like below
CONFIG:
LOAD
MADE ,
[Metal Part ID],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
EQPID_Map:
Mapping
LOAD [Eqpt ID],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
RECIPE_Map:
Mapping
LOAD
[Recipe Name],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
GA_TEMP:
LOAD
DATE(REPORT_DATE) AS REPORT_DATE,
REPORT_MONTH,
PC_EQP_TYPE,
MOVE_QTY,
RECIPE_NAME,
EQPID,
ApplyMap('RECIPE_Map',RECIPE_NAME,Null() ) AS RECIPE_LinkKey,
ApplyMap('EQPID_Map',EQPID,Null()) AS EQPID_LinkKey
FROM $(vInputPath)
(ooxml, embedded labels, table is DATA)
where REPORT_MONTH =10;
NoConcatenate
GA_PRE:
LOAD *,
if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS LINK_KEY
RESIDENT GA_TEMP;
DROP TABLE GA_TEMP;
Join(CONFIG)
Load Distinct LINK_KEY, REPORT_DATE Resident GA_PRE;
Load MADE ,
[Metal Part ID],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY,
REPORT_DATE &'-'& [Metal Part ID] as Key
Resident CONFIG;
DROP Table CONFIG;
DW:
LOAD
MADE AS MA,
DATE(FLOOR(TRANS_PERIOD)) AS TRANS_PERIOD,
daystart(TRANS_PERIOD, 0, 1 ) AS RD,
DATE(FLOOR(TRANS_PERIOD)) &'-'& PART_NUMBER as Key,
EVENT_DATE,
PART_NUMBER,
TOTALVALUE
FROM
$(vInputPath)
(ooxml, embedded labels, table is DW);
HI
Can some one please help on this...!!
Thanks
try below script Lakshman:
CONFIG:
LOAD
MADE ,
[Metal Part ID],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
Concatenate
LOAD
MADE AS MA,
DATE(FLOOR(TRANS_PERIOD)) AS TRANS_PERIOD,
daystart(TRANS_PERIOD, 0, 1 ) AS REPORT_DATE,
EVENT_DATE,
PART_NUMBER as [Metal Part ID],
TOTALVALUE
FROM
$(vInputPath)
(ooxml, embedded labels, table is DW);
NEW_CONFIG:
Load MADE ,
[Metal Part ID],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY & REPORT_DATE as NEW_LINK,
MA,
TRANS_PERIOD,
REPORT_DATE,
EVENT_DATE,
TOTALVALUE
Resident CONFIG;
drop table CONFIG;
EQPID_Map:
Mapping
LOAD [Eqpt ID],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
RECIPE_Map:
Mapping
LOAD
[Recipe Name],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
GA_TEMP:
LOAD
DATE(REPORT_DATE) AS REPORT_DATE,
REPORT_MONTH,
PC_EQP_TYPE,
MOVE_QTY,
RECIPE_NAME,
EQPID,
ApplyMap('RECIPE_Map',RECIPE_NAME,Null() ) AS RECIPE_LinkKey,
ApplyMap('EQPID_Map',EQPID,Null()) AS EQPID_LinkKey
FROM $(vInputPath)
(ooxml, embedded labels, table is DATA)
where REPORT_MONTH =10;
NoConcatenate
GA_PRE:
LOAD LINK_KEY&REPORT_DATE AS NEW_LINK,
*;
LOAD *,
if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS LINK_KEY
RESIDENT GA_TEMP;
DROP Fields LINK_KEY, REPORT_DATE;
DROP TABLE GA_TEMP;
@raajview : Its not working as expected.. 😞
Linking is not working properly.
Try the attached one..
Hi...
I don't really understand the subject area and therefore can offer 2 different solutions.
1.if the relationship of the tables: DW and CONFIG should be only in the field PART_NUMBER ([Metal Part Id]) regardless of DATA, then I think the solution is only to use Set Analysis
2.
If you assume that the result is the intersection of the keys of all three tables (link_key, report_key, part_number (metal part ID), I think it was right to make a link table.
I slightly changed your script to implement this idea, but did not optimize it. Please compare. I hope it helps.
Because the model has changed for dashboard for TB10 I specified not to display rows with a NULL field for the field EQPID
Script:
CONFIG:
LOAD
MADE ,
[Metal Part ID] AS [Link_PN2],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY AS [Link_LK2]
,[LINK_KEY]&'|'&[Metal Part ID] AS [Link_LK2+PN2]
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
EQPID_Map:
Mapping
LOAD [Eqpt ID],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
RECIPE_Map:
Mapping
LOAD
[Recipe Name],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
GA_TEMP:
LOAD
DATE(REPORT_DATE) AS [Link_RD2],
REPORT_MONTH,
PC_EQP_TYPE,
MOVE_QTY,
RECIPE_NAME,
EQPID,
ApplyMap('RECIPE_Map',RECIPE_NAME,Null() ) AS RECIPE_LinkKey,
ApplyMap('EQPID_Map',EQPID,Null()) AS EQPID_LinkKey
FROM $(vInputPath)
(ooxml, embedded labels, table is DATA)
where REPORT_MONTH =10;
NoConcatenate
GA_PRE:
LOAD *,
if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS [Link_LK1]
,[Link_RD2]&'|'&if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS [Link_RD2+LK1]
RESIDENT GA_TEMP;
DROP TABLE GA_TEMP;
DW:
LOAD
MADE AS MA,
DATE(FLOOR(TRANS_PERIOD)) AS TRANS_PERIOD,
Date(daystart(TRANS_PERIOD, 0, 1 )) AS [Link_RD1],
EVENT_DATE,
PART_NUMBER AS [Link_PN1],
TOTALVALUE
,Date(daystart(TRANS_PERIOD, 0, 1 )) &'|'&[PART_NUMBER] AS [Link_RD1+PN1]
FROM
$(vInputPath)
(ooxml, embedded labels, table is DW);
[LinkTable]:
Load Distinct
[Link_RD1] AS [REPORT_DATE]
,[Link_PN1] AS [PART_NUMBER]
,[Link_RD1] &'|'&[Link_PN1] AS [Link_RD1+PN1]
Resident DW;
Outer Join([LinkTable])
Load Distinct
[Link_RD2] AS [REPORT_DATE]
,[Link_LK1] AS [LINK_KEY]
,[Link_RD2]&'|'&[Link_LK1] AS [Link_RD2+LK1]
Resident GA_PRE;
Outer Join([LinkTable])
Load Distinct
[Link_LK2] AS [LINK_KEY]
,[Link_PN2] AS [PART_NUMBER]
,[Link_PN2] AS [Metal Part ID]
,[Link_LK2]&'|'&[Link_PN2] AS [Link_LK2+PN2]
Resident CONFIG;
Drop Fields [Link_PN1], [Link_RD1], [Link_RD2],[Link_LK1],[Link_LK2],[Link_PN2];
Try like below
CONFIG:
LOAD
MADE ,
[Metal Part ID],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
EQPID_Map:
Mapping
LOAD [Eqpt ID],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
RECIPE_Map:
Mapping
LOAD
[Recipe Name],
LINK_KEY
FROM
$(vInputPath)
(ooxml, embedded labels, table is CONFIG);
GA_TEMP:
LOAD
DATE(REPORT_DATE) AS REPORT_DATE,
REPORT_MONTH,
PC_EQP_TYPE,
MOVE_QTY,
RECIPE_NAME,
EQPID,
ApplyMap('RECIPE_Map',RECIPE_NAME,Null() ) AS RECIPE_LinkKey,
ApplyMap('EQPID_Map',EQPID,Null()) AS EQPID_LinkKey
FROM $(vInputPath)
(ooxml, embedded labels, table is DATA)
where REPORT_MONTH =10;
NoConcatenate
GA_PRE:
LOAD *,
if(len(RECIPE_LinkKey)=0,EQPID_LinkKey,RECIPE_LinkKey) AS LINK_KEY
RESIDENT GA_TEMP;
DROP TABLE GA_TEMP;
Join(CONFIG)
Load Distinct LINK_KEY, REPORT_DATE Resident GA_PRE;
Load MADE ,
[Metal Part ID],
[Eqpt ID] ,
[Eqpt Group],
[Recipe Name] ,
LINK_KEY,
REPORT_DATE &'-'& [Metal Part ID] as Key
Resident CONFIG;
DROP Table CONFIG;
DW:
LOAD
MADE AS MA,
DATE(FLOOR(TRANS_PERIOD)) AS TRANS_PERIOD,
daystart(TRANS_PERIOD, 0, 1 ) AS RD,
DATE(FLOOR(TRANS_PERIOD)) &'-'& PART_NUMBER as Key,
EVENT_DATE,
PART_NUMBER,
TOTALVALUE
FROM
$(vInputPath)
(ooxml, embedded labels, table is DW);
Thank You very much @raajview @Siva_Sankar @DenisKolobov @MayilVahanan for the fast and quick response..