topic Re: Cross Table with dynamic columns in App Development

Cross Table with dynamic columns

normfred3 — Sun, 25 Feb 2018 23:33:36 GMT

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

RecordID	A	B	C	D	E
4001	abc			1
4002	qwe	1	1		1
4003	asd		1		1
4004	zxc			1
4005	poi	1		1	1
4006	lkj		1
4007	mnb			1	1
4008	rty	1
4009	fgh	1	1
4010	vbn	1			1

Column Table

ColumnID

Re: Cross Table with dynamic columns

kakani87 — Mon, 26 Feb 2018 06:20:30 GMT

Something like this ?

Re: Cross Table with dynamic columns

normfred3 — Mon, 26 Feb 2018 12:09:36 GMT

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

Re: Cross Table with dynamic columns

kakani87 — Mon, 26 Feb 2018 17:21:54 GMT

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)

Re: Cross Table with dynamic columns

kakani87 — Mon, 26 Feb 2018 17:24:58 GMT

Correction in the post for list box expression

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

Re: Cross Table with dynamic columns

normfred3 — Mon, 26 Feb 2018 18:44:29 GMT

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