topic how to pivot in load script in App Development

how to pivot in load script

skyline01 — Thu, 23 Dec 2021 18:19:25 GMT

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.

Re: how to pivot in load script

sunny_talwar — Wed, 28 Nov 2018 16:41:14 GMT

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;

Re: how to pivot in load script

Frank_Hartmann — Wed, 28 Nov 2018 16:43:38 GMT

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;

Re: how to pivot in load script

skyline01 — Wed, 28 Nov 2018 16:57:31 GMT

@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.

Re: how to pivot in load script

skyline01 — Wed, 28 Nov 2018 16:57:04 GMT

@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?

Re: how to pivot in load script

sunny_talwar — Wed, 28 Nov 2018 17:01:55 GMT

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 🙂

Re: how to pivot in load script

Gysbert_Wassenaar — Wed, 28 Nov 2018 17:04:39 GMT

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

Re: how to pivot in load script

sunny_talwar — Wed, 28 Nov 2018 17:06:47 GMT

Very well said

Re: how to pivot in load script

skyline01 — Wed, 28 Nov 2018 18:37:24 GMT

@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.)

Re: how to pivot in load script

Gysbert_Wassenaar — Wed, 28 Nov 2018 19:01:01 GMT

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

Re: how to pivot in load script

Gysbert_Wassenaar — Thu, 29 Nov 2018 17:09:46 GMT

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.