topic Re: Pivot columns to rows in QlikView

Pivot columns to rows

Anonymous — Thu, 12 Apr 2018 20:37:30 GMT

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.

State	08_31_2017	09_29_2017	10_31_2017
NY	100	200	300
NJ	50	150	250
MD	75	225	375

desired output :

Date	State	Sales
08_31_2017	NY	100
09_29_2017	NY	200
10_31_2017	NY	300
08_31_2017	NJ	50
09_29_2017	NJ	150
10_31_2017	NJ	250
08_31_2017	MD	75
09_29_2017	MD	225
10_31_2017	MD	375

Re: Pivot columns to rows

petter — Thu, 12 Apr 2018 20:44:47 GMT

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

CROSSTABLE(Date,Sales) LOAD

STATE,

[08_31_2017],

[09_29_2017],

.....

FROM

.....

;

Re: Pivot columns to rows

Anonymous — Thu, 12 Apr 2018 20:49:41 GMT

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

Re: Pivot columns to rows

vishsaggi — Thu, 12 Apr 2018 21:41:06 GMT

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.

Re: Pivot columns to rows

Anonymous — Thu, 12 Apr 2018 22:54:25 GMT

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?

Re: Pivot columns to rows

vishsaggi — Fri, 13 Apr 2018 00:51:37 GMT

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.

Re: Pivot columns to rows

YoussefBelloum — Fri, 13 Apr 2018 07:59:19 GMT

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.

Re: Pivot columns to rows

Anonymous — Fri, 13 Apr 2018 09:28:46 GMT

Thanks Petter,Vishwarath and Youssef.

Much appreciated.

Re: Pivot columns to rows

YoussefBelloum — Fri, 13 Apr 2018 09:45:03 GMT

You're welcome

Good luck