Skip to main content

App Development

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
normfred3
Contributor
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
Specialist
Specialist

Something like this ?

CrossTable.png

normfred3
Contributor
Contributor
Author

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

kakani87
Specialist
Specialist

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
Specialist
Specialist

Correction in the post for list box expression

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

normfred3
Contributor
Contributor
Author

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