Load Row Data into columns

[farru_scorpio]

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.

[Mark_Little]

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

[farru_scorpio]

Dear Mark,

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

[elvis_schwarz · Accepted solution]

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;

[farru_scorpio]

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