Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
petter
Partner - Champion III
Partner - Champion III

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
petter
Partner - Champion III
Partner - Champion III

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

CROSSTABLE(Date,Sales) LOAD

STATE,

[08_31_2017],

[09_29_2017],

.....

FROM

  .....

;

Anonymous
Not applicable
Author

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

vishsaggi
Champion III
Champion III

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.

Anonymous
Not applicable
Author

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?

vishsaggi
Champion III
Champion III

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.

YoussefBelloum
Champion
Champion

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.

Anonymous
Not applicable
Author

Thanks Petter,Vishwarath and Youssef.

Much appreciated.

YoussefBelloum
Champion
Champion

You're welcome

Good luck