Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.