topic Transpose a table based on Year Field in QlikView

Transpose a table based on Year Field

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

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

Re: Transpose a table based on Year Field

Anil_Babu_Samineni — Wed, 17 Jan 2018 11:38:47 GMT

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

Re: Transpose a table based on Year Field

MarcoWedel — Wed, 17 Jan 2018 21:43:31 GMT

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

Re: Transpose a table based on Year Field

ashish_2511 — Thu, 18 Jan 2018 13:59:16 GMT

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

Re: Transpose a table based on Year Field

Anonymous — Fri, 19 Jan 2018 03:07:55 GMT

Hi Marco,

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

but how to get just one table only?