Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
gauravgg
Not applicable

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

Tags (2)
2 Replies
Gysbert_Wassenaar
Not applicable

Re: how to convert to cross table ?

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
Not applicable

Re: how to convert to cross table ?

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