Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Hi,
Perhaps look at crosstable if it might work for you:
It works
thank you for the fast reponse