Transforming the table

[Anonymous]

Hi

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 · Accepted solution]

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]

Hi,

Perhaps look at crosstable if it might work for you:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

[Anonymous]

It works

thank you for the fast reponse