Association between different values of a field

[kumar_72]

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

[vchuprina]

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

[kumar_72]

@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

[vchuprina · Accepted solution]

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