Qlik Community

Qlik Sense App Development

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

normfred3
New Contributor

Cross Table with dynamic columns

I have a records table (records) that I want to dynamically load individual columns based on another table (column).

my original table has hundreds of columns.

I need a cross table with only the specific columns in the column table.

I am trying to create a loop but I have been unsuccessful.

Is this even possible?

Record  Table

RecordIDABCDE
4001abc 1
4002qwe11 1
4003asd 1 1
4004zxc 1
4005poi1 11
4006lkj 1
4007mnb 11
4008rty1
4009fgh11
4010vbn1 1

Column Table

ColumnID
B
C
E
5 Replies
kakani87
Contributor III

Re: Cross Table with dynamic columns

Something like this ?

CrossTable.png

normfred3
New Contributor

Re: Cross Table with dynamic columns

that looks correct - what does the script look like in qliksense?

kakani87
Contributor III

Re: Cross Table with dynamic columns

Script Syntax

Temp:

CrossTable(Prod,Val)

load * inline [

RecordID,A,B,C,D,E

401,1,1,1,1,1

402,1,0,0,1,1

403,0,0,0,1,1

404,0,0,1,1,0

405,1,0,1,0,1

406,0,1,0,1,0

];

After loading this script now in frontend List box add measure as

=if(match(Prod,'B','C','D'),Prod)

kakani87
Contributor III

Re: Cross Table with dynamic columns

Correction in the post for list box expression

=if(match(Prod,'B','C','E'),Prod)

normfred3
New Contributor

Re: Cross Table with dynamic columns

Thanks Kakani, but I am in need of a dynamic load script to create the cross table.

I have a large table if records with many other attributes.  I also have a second table (columns) that I need to use as the reference for the data load from the records table.  The columns table will change weekly so I will need to update the columns table.  my records table is thousands of rows and hundreds of columns. 

I am new to qliksense and I am struggling with looping variables and referencing.

FOR Each a in FieldValueList('Columns')

LOAD '$(a) as NEWFIELD AutoGenerate 2;

NEXT a