Transpose a table based on Year Field

[ashish_2511]

Hi There,

Following are my input and output tables. Please advise a way to achieve this.

Basically A,B ... have same numbers for all years, only Y,Z and U change. So I want a single row instead of 3 rows with new fields as y2016,y2018.. etc.

Note -  I want to achieve this at the back end in my data model

Input -

A	B	year	Y	Z	U
1	2	2016	4	7	10
1	2	2017	5	8	11
1	2	2018	6	9	12

Output -

A	B	y2016	y2017	y2018	z2016	z2017	z2018	u2016	u2017	u2018
1	2	4	5	6	7	8	9	10	11	12

Thanks In advance

-Sneh

[Anil_Babu_Samineni]

Try to implement like How to convert column into row in qlikview sql ?

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

tabTemp:
CrossTable (ColNam, ColVal, 3)
LOAD * FROM [https://community.qlik.com/thread/288079] (html, codepage is 1252, embedded labels, table is @1);

tabResult:
Generic
LOAD A,
    B,
    ColNam&year,
    ColVal
Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

[ashish_2511]

Exactly what I was looking for. Thanks a lot Marco.

[Anonymous]

Hi Marco,

I like your solution. In your sample qvw, it will generate many tables

but how to get just one table only?