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: 
gauravgg
Partner - Creator
Partner - Creator

how to convert to cross table ?

Hi

I have the following table

i want to convert the "Original" table to "Output" table in the script .

Original Table

IDCategorySubCategorytype
1Aa1,a2,a3Z
2Bb1,b2,b3N
3Cc1,c2Q
4Aa1,a2X

Output Table

IDABCtype
1a1,a2,a3Z
2b1,b2,b3N
3c1,c2Q
4a1,a2X

can anyone help me

Thanks in Advance

2 Replies
Gysbert_Wassenaar

Sounds like a bad idea to me, but if you insist you can try The Generic Load


talk is cheap, supply exceeds demand
sunny_talwar

I agree with gwassenaar‌, but here is another solution

Table:

LOAD * INLINE [

    ID, Category, SubCategory, type

    1, A, "a1,a2,a3", Z

    2, B, "b1,b2,b3", N

    3, C, "c1,c2", Q

    4, A, "a1,a2", X

];

FinalTable:

LOAD ID,

  type

Resident Table;

FOR i = 1 to FieldValueCount('Category')

  LET vField = FieldValue('Category', $(i));

  Left Join (FinalTable)

  LOAD ID,

  type,

  SubCategory as [$(vField)]

  Resident Table

  Where Category = '$(vField)';

NEXT

DROP Table Table;

Capture.PNG