Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
lakshmanvepuri
Creator
Creator

To Create the Link without Circular Loop

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.

lakshmanvepuri_0-1609608817619.png

 

lakshmanvepuri_1-1609608831418.png

 

 

Script and Raw Data is enclosed.

Thanks

LK 

Labels (3)
2 Solutions

Accepted Solutions
DenisKolobov
Contributor
Contributor

loop without loop.pngHi...

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];

View solution in original post

MayilVahanan

Hi @lakshmanvepuri 

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);

MayilVahanan_0-1610273702392.png

 

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

7 Replies
lakshmanvepuri
Creator
Creator
Author

HI 

Can some one please help on this...!!

 

Thanks

raajview
Contributor
Contributor

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;

 

lakshmanvepuri
Creator
Creator
Author

@raajview : Its not working as expected.. 😞

Linking is not working properly. 

Siva_Sankar
Master II
Master II

Try the attached one..

DenisKolobov
Contributor
Contributor

loop without loop.pngHi...

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];

MayilVahanan

Hi @lakshmanvepuri 

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);

MayilVahanan_0-1610273702392.png

 

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

Thank You very much  @raajview @Siva_Sankar @DenisKolobov @MayilVahanan  for the fast and quick response..