Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
quser_learn
New Contributor III

Pivot columns to rows

I have a csv dateset with 36 month-ends horizontally and would like to convert it to rows. See below sample dataset.

any load script ideas?

   csv dataset.

State08_31_201709_29_201710_31_2017
NY100200300
NJ50150250
MD75225375

desired output :

 

DateStateSales
08_31_2017NY100
09_29_2017NY200
10_31_2017NY300
08_31_2017NJ50
09_29_2017NJ150
10_31_2017NJ250
08_31_2017MD75
09_29_2017MD225
10_31_2017MD375
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Pivot columns to rows

You can make use of the CrossTable prefix for the Load statement:

CROSSTABLE(Date,Sales) LOAD

STATE,

[08_31_2017],

[09_29_2017],

.....

FROM

  .....

;

View solution in original post

8 Replies
Highlighted
MVP
MVP

Re: Pivot columns to rows

You can make use of the CrossTable prefix for the Load statement:

CROSSTABLE(Date,Sales) LOAD

STATE,

[08_31_2017],

[09_29_2017],

.....

FROM

  .....

;

View solution in original post

Highlighted
quser_learn
New Contributor III

Re: Pivot columns to rows

Thanks -  any ideas do it automatically when new month end date is added to the file without modifying the script?

vishsaggi
Esteemed Contributor III

Re: Pivot columns to rows

New month end dates means new rows added to the file. you do not have to change anything to the script what petter suggested. Use his script and when ever the new rows added to the file and you run this script, it will take care of the front end.

Highlighted
quser_learn
New Contributor III

Re: Pivot columns to rows

VIshwarath, Peters solution works If the input file is static.

But next month when I get the input file with one more month end (one extra column ) don’t I have to manually add the date to script?

Highlighted
vishsaggi
Esteemed Contributor III

Re: Pivot columns to rows

You can try this.

tabFields:

LOAD *

FROM

CrossTab.xlsx

(ooxml, embedded labels, table is Sheet1)

;

FinalCross:

CrossTable(DateField, Sales, 1)

LOAD *

Resident tabFields;

Drop Table tabFields;

So just add some dummy date field in your excel and just refresh this above script it should automatically pick up all the values.

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Pivot columns to rows

Hi,

one thing is crucial, the positions of the columns before the first Date field must don't move, so here the STATE column should always comes first. then you can add as much Date columns as you want.

Highlighted
quser_learn
New Contributor III

Re: Pivot columns to rows

Thanks Petter,Vishwarath and Youssef.

Much appreciated.

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Pivot columns to rows

You're welcome

Good luck