Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I want to create association between different values of a field(ID) based on another field(Link). I have 3 fields ID, Link and Type. From below data we can see that T2 has Link of T1, It means T2 is connected with T1, T3 has Link of T2, it means T3 is connected with T2, T4 has Link of T3, it means T4 is connected with T3, similarly for D1, D2 and D3.
T1--> T2-->T3-->T4
D1-->D2-->D3
I want to create four different fields XR, XW, XZ, XK to show above association in a table . If there is no Link for a value in ID field then 'No Link' should appear.
Sample Data
ID | Link | Type |
T1 | XR | |
T2 | T1 | XW |
T3 | T2 | XZ |
T4 | T3 | XK |
D1 | XW | |
D2 | D1 | XZ |
D3 | D2 | XK |
Output
XR | XW | XZ | XK |
T1 | T2 | T3 | T4 |
No Link | D1 | D2 | D3 |
Hi,
Try this, I'm not sure that this is the most elegant way to solve your issue, but it works.
Sample:
LOAD
ID,
Link,
Type;
LOAD * Inline[
ID, Link, Type
T1, , XR
T2, T1, XW
T3, T2, XZ
T4, T3, XK
D1, , XW
D2, D1, XZ
D3, D2, XK
D7, , XZ
D8, D7, XK
T5, , XW
T6, T5, XZ
T7, T6, XK
D10, , XZ
T9, D10, XK
];
Generic:
GENERIC LOAD
Link,
Type,
ID
RESIDENT Sample;
DROP Table Sample;
//XR is always in the first place
AssociationsTMP:
LOAD Distinct XR
RESIDENT [Generic.XR];
DROP Table [Generic.XR];
//I guess you have something similar to hierarchy,
//so we should load data in the correct order
TableList:
LOAD * Inline [
TableList,
Generic.XW,
Generic.XZ,
Generic.XK
];
LET vFieldName = 'XR'; //for the first loop
FOR Each vTable in FieldValueList('TableList')
LET vField=SubField(vTable, '.', -1);
OUTER JOIN (AssociationsTMP)
LOAD
Link AS [$(vFieldName)],
[$(vField)]
RESIDENT [$(vTable)];
LET vFieldName = '$(vField)';
DROP TABLE [$(vTable)];
NEXT vTable
DROP Table TableList;
Associations:
NoConcatenate
LOAD
IF(IsNull(XR) or XR='', 'No Link', XR) AS XR,
IF(IsNull(XW) or XW='', 'No Link', XW) AS XW,
IF(IsNull(XZ) or XZ='', 'No Link', XZ) AS XZ,
IF(IsNull(XK) or XK='', 'No Link', XK) AS XK
Resident AssociationsTMP;
DROP Table AssociationsTMP;
Result:
Regards,
Vitalii
Hi,
Try the following:
Sample:
LOAD
PurgeChar(ID, '0123456789') AS TypeID,
ID,
Link,
Type;
LOAD * Inline[
ID, Link, Type
T1, , XR
T2, T1, XW
T3, T2, XZ
T4, T3, XK
D1, , XW
D2, D1, XZ
D3, D2, XK
];
Generic:
GENERIC LOAD
TypeID,
Type,
ID
RESIDENT Sample;
AssociationsTMP:
LOAD Distinct TypeID
Resident Sample;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'Generic.*') THEN
LEFT JOIN (AssociationsTMP) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
DROP Table Sample;
Associations:
NoConcatenate
LOAD
TypeID,
IF(IsNull(XR), 'No Link', XR) AS XR,
IF(IsNull(XW), 'No Link', XW) AS XW,
IF(IsNull(XZ), 'No Link', XZ) AS XZ,
IF(IsNull(XK), 'No Link', XK) AS XK
Resident AssociationsTMP;
DROP Table AssociationsTMP;
Result:
Also, read this article:
https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/
Regards,
Vitalii
@vchuprina thanks for the response
above solution is working fine only when there is single instance of each TypeID (T1, T2, T3,T4), (D1,D2,D3), but in actual scenario data is like below. Please help
ID, Link, Type
T1, , XR
T2, T1, XW
T3, T2, XZ
T4, T3, XK
D1, , XW
D2, D1, XZ
D3, D2, XK
D7, , XZ
D8, D7,XK
T5, , XW
T6, T5, XZ
T7, T6, XK
D10, ,XZ
T9, D10,XK
Hi,
Try this, I'm not sure that this is the most elegant way to solve your issue, but it works.
Sample:
LOAD
ID,
Link,
Type;
LOAD * Inline[
ID, Link, Type
T1, , XR
T2, T1, XW
T3, T2, XZ
T4, T3, XK
D1, , XW
D2, D1, XZ
D3, D2, XK
D7, , XZ
D8, D7, XK
T5, , XW
T6, T5, XZ
T7, T6, XK
D10, , XZ
T9, D10, XK
];
Generic:
GENERIC LOAD
Link,
Type,
ID
RESIDENT Sample;
DROP Table Sample;
//XR is always in the first place
AssociationsTMP:
LOAD Distinct XR
RESIDENT [Generic.XR];
DROP Table [Generic.XR];
//I guess you have something similar to hierarchy,
//so we should load data in the correct order
TableList:
LOAD * Inline [
TableList,
Generic.XW,
Generic.XZ,
Generic.XK
];
LET vFieldName = 'XR'; //for the first loop
FOR Each vTable in FieldValueList('TableList')
LET vField=SubField(vTable, '.', -1);
OUTER JOIN (AssociationsTMP)
LOAD
Link AS [$(vFieldName)],
[$(vField)]
RESIDENT [$(vTable)];
LET vFieldName = '$(vField)';
DROP TABLE [$(vTable)];
NEXT vTable
DROP Table TableList;
Associations:
NoConcatenate
LOAD
IF(IsNull(XR) or XR='', 'No Link', XR) AS XR,
IF(IsNull(XW) or XW='', 'No Link', XW) AS XW,
IF(IsNull(XZ) or XZ='', 'No Link', XZ) AS XZ,
IF(IsNull(XK) or XK='', 'No Link', XK) AS XK
Resident AssociationsTMP;
DROP Table AssociationsTMP;
Result:
Regards,
Vitalii