topic Re: Transforming the table in QlikView

Transforming the table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have file with 5 column.

example:

Year	City	Type	Sales	Quantity
2017	London	A	11	2
2017	London	B	12	3
2018	Liverpool	A	13	4
2018	Liverpool	B	15	5

and i need this tabel

Year	City	Sales_A	Sales_B	Quantity_A	Quantity_B
2017	London	11	12	2	3
2018	Liverpool	13	15	4	5

Please help

sunny_talwar — Fri, 22 Jun 2018 16:24:45 GMT

Try this

Table:

LOAD * INLINE [

Year, City, Type, Sales, Quantity

2017, London, A, 11, 2

2017, London, B, 12, 3

2018, Liverpool, A, 13, 4

2018, Liverpool, B, 15, 5

];

FinalTable:

LOAD Distinct Year,

City

Resident Table;

FOR i = 1 to FieldValueCount('Type')

LET vType = FieldValue('Type', $(i));

Left Join (FinalTable)

LOAD Distinct Year,

City,

Sales as [Sales_$(vType)],

Quantity as [Quantity_$(vType)]

Resident Table

Where Type = '$(vType)';

NEXT

DROP Table Table;

Anonymous — Mon, 25 Jun 2018 10:51:42 GMT

Hi,

Perhaps look at crosstable if it might work for you:

Anonymous — Wed, 27 Jun 2018 10:14:28 GMT

It works

thank you for the fast reponse