topic Re: To Create the Link without Circular Loop in QlikView

To Create the Link without Circular Loop

lakshmanvepuri — Sat, 16 Nov 2024 17:39:28 GMT

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

Re: To Create the Link without Circular Loop

lakshmanvepuri — Sun, 03 Jan 2021 16:35:09 GMT

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

Thanks

Re: To Create the Link without Circular Loop

raajview — Mon, 04 Jan 2021 08:16:36 GMT

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;

Re: To Create the Link without Circular Loop

lakshmanvepuri — Mon, 04 Jan 2021 13:54:34 GMT

@raajview : Its not working as expected.. 😞

Linking is not working properly.

Re: To Create the Link without Circular Loop

Siva_Sankar — Tue, 05 Jan 2021 08:19:25 GMT

Try the attached one..

Re: To Create the Link without Circular Loop

DenisKolobov — Sun, 10 Jan 2021 10:24:36 GMT

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

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

Re: To Create the Link without Circular Loop

MayilVahanan — Sun, 10 Jan 2021 10:15:12 GMT

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

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

Re: To Create the Link without Circular Loop

lakshmanvepuri — Fri, 15 Jan 2021 10:35:51 GMT

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