Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
skyline01
Creator
Creator

how to pivot in load script

I am working in Qlik Sense Enterprise 3.2.  I have a data source that looks like the following:

 

Test:
Load
*
Inline
[
Key, State
1, a
1, a
1, b
2, a
3, c
3, d
3, d
]
;

Within the load editor, I want to pivot this table on Key, turning my State values into column headers, whose values are populated by a count of Key.  So, I want my output to look like:

Key     a     b     c     d

1           2     1     0     0

2           1     0     0     0

3            0    0     1     2

How can I achieve this output in the load editor?  I don't think CrossTable or Generic will work.  CrossTable is a simple transposition (lacking the aggregation that I want) of a wide table into a tall table (which is the opposite direction of what I want).  Generic does turn a tall table into a wide table (which is the direction that I want), but it's a simple transposition (again, no aggregation).  In Excel or SQL, I would pivot the data.  However, I cannot perform this transformation outside of Qlik Sense.

10 Replies
Gysbert_Wassenaar

Here's another alternative:

Test:
Load *		
Inline
[
Key, State
1, a
1, a
1, b
2, a
3, c
3, d
3, d
]
;

LET vFieldList = '';

For i = 1 to FieldValueCount('State')

	LET vField = FieldValue('State',$(i));
	LET vFieldList = '$(vFieldList), count(if(State=' & chr(39) & '$(vField)'  & chr(39) & ', [State])) as [$(vField)]';

Next


Result:
LOAD
	Key $(vFieldList)
Resident
	Test
Group By
	Key
	;		

 This might work a bit faster on larger data sets or if you have a lot of State values.


talk is cheap, supply exceeds demand