Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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..