how to pivot in load script

[skyline01]

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.

[sunny_talwar · Accepted solution]

Try this

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

Left Join (Test)
LOAD Key,
	 State,
	 Count(State) as Count
Resident Test
Group By Key, State;

FinalTable:
LOAD DISTINCT Key
Resident Test;

FOR i = 1 to FieldValueCount('State')

	LET vState = FieldValue('State', $(i));
	
	Left Join (FinalTable)
	LOAD DISTINCT Key,
		 Count as [$(vState)]
	Resident Test
	Where State = '$(vState)';
	
NEXT

DROP Table Test;

[Frank_Hartmann]

try this:

Generic 
Load *,count(Key) as Dummy
Inline
[
Key, State
1, a
1, a
1, b
2, a
3, c
3, d
3, d
]
Group by Key,State;

[skyline01]

@Frank_Hartmann

That solution seems to be incomplete.  I need the output to be a single table.  That solution breaks up the output into separate tables, organized by non-zero State counts.  I might be able to re-shape it back into 1 table, though.

[skyline01]

@sunny_talwar

Your solution works.  However, that seems to be a lot of code for something that seems pretty simple (e.g., I can do this with just a few lines in SQL).  Is your solution the usual way to resolve this kind of situation?

[sunny_talwar]

It is not, the usual way is to use generic load.... but to combine them into a single table... one way or the other... you will need to use loop 🙂

[Gysbert_Wassenaar]

Hi @skyline01

We can do it with one expression of 10 characters in a pivot table object. And that's exactly where you should do this kind of thing if you're using Qlik Sense. Why do you need to do this in the load script? 

Qlik Sense is quite versatile, but some kind of data transformation tricks can be executed by other specialist data munging tools with less lines of code. If you can do it in a couple of lines of sql then why don't you. Qlik Sense is perfectly capable of sending such a statement to a rdbms and receiving the results.

Kind regards,
Gysbert

[sunny_talwar]

Very well said

[skyline01]

@Gysbert_Wassenaar

I know this can be done fairly easily in a visualization (if that is what you mean by "pivot table object").  I need it done in a load script since it's actually a working / intermediate table for a table that I eventually load into the model.  (In reality, I will be first joining 2 tables, pivoting the result, creating a calculated column, filtering the result, and then joining the result to yet another table.  I only need to load this final derived table into the model.)

And, in general, I would push down this kind of transformation into the database.  My problem is that I have severe querying restrictions put onto me by the DBAs of the data source.  (The actual source is ServiceNow, and I'm running this against a large table.  The only connection mechanism that I have available to me is ODBC, which is incredibly slow.  So, the DBAs want me to only run very simple and highly filtered queries, i.e., I can't run a pivot.)

[Gysbert_Wassenaar]

HI @skyline01,

Thanks for explaining the situation. I understand the constraints you have to work with. Unfortunately I don't have a better solution than @sunny_talwar posted already. I hope it works for you.

Kind regards,
Gysbert