Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ashish_2511
Contributor

Transpose a table based on Year Field

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 -

   

AByearYZU
1220164710
1220175811
1220186912

Output -

   

ABy2016y2017y2018z2016z2017z2018u2016u2017u2018
12456789101112

Thanks In advance

-Sneh

1 Solution

Accepted Solutions

Re: Transpose a table based on Year Field

Hi,

one solution might be:

QlikCommunity_Thread_288079_Pic1.JPG

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

View solution in original post

4 Replies

Re: Transpose a table based on Year Field

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Transpose a table based on Year Field

Hi,

one solution might be:

QlikCommunity_Thread_288079_Pic1.JPG

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

View solution in original post

ashish_2511
Contributor

Re: Transpose a table based on Year Field

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

mysail25
Contributor

Re: Transpose a table based on Year Field

Hi Marco,

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

but how to get just one table only?