topic Re: Load Row Data into columns in Management & Governance

Load Row Data into columns

farru_scorpio — Fri, 14 Jul 2023 11:01:03 GMT

Dear Qlikers,

As a beginner, I am stuck here where I need something like described as below in two tables: Where in table "What I have" which is how data is available and loaded into the table but instead of this, I want to convert it into column fields based on the common key which will remain the same in any situation.

What I have
Key	product	count
6	Air	1
48	Air	1
2	Air	0
4	Air	1
9	Air	0
6	Hotel	0
48	Hotel	1
2	Hotel	1
4	Hotel	0
9	Hotel	1
6	Car	0
48	Car	0
2	Car	0
4	Car	1
9	Car	0

What I need
Key	Air	Hotel	Car	Total
6	1	0	0	1
48	1	1	0	2
2	0	1	0	1
4	1	0	1	2
9	0	1	0	1

I appreciate all the helpers.

Re: Load Row Data into columns

Mark_Little — Fri, 14 Jul 2023 12:11:12 GMT

HI,

Take a look at the crosstable function in script.

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/crosstable.htm

Re: Load Row Data into columns

farru_scorpio — Thu, 20 Jul 2023 08:08:48 GMT

Dear Mark,

Thank you for sharing this link, but I need vice versa results exactly the same shown in given examples above.

Re: Load Row Data into columns

elvis_schwarz — Thu, 20 Jul 2023 15:21:17 GMT

try:

Test1:

Load * Inline [

Key, product, count

6 ,Air ,1

48 ,Air ,1

2 ,Air ,0

4 ,Air ,1

9 ,Air ,0

6 ,Hotel ,0

48 ,Hotel ,1

2 ,Hotel ,1

4 ,Hotel ,0

9 ,Hotel ,1

6 ,Car ,0

48 ,Car ,0

2 ,Car ,0

4 ,Car ,1

9 ,Car ,0

];

For Each vProduct in FieldValueList('product')

Trace $(vProduct);

Test2:

Load Key,

count as '$(vProduct)'

Resident Test1

Where WildMatch(product,'$(vProduct)');

Next vProduct

Left Join(Test2)

Load

Key,

Sum(count) as Total

RESIDENT Test1

GROUP BY Key

;

Drop table Test1;

Re: Load Row Data into columns

farru_scorpio — Fri, 28 Jul 2023 06:57:09 GMT

Much appreciated, Dear Elvis. This is exactly what I need. Thank you.