Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
You can make use of the CrossTable prefix for the Load statement:
CROSSTABLE(Date,Sales) LOAD
STATE,
[08_31_2017],
[09_29_2017],
.....
FROM
.....
;
You can make use of the CrossTable prefix for the Load statement:
CROSSTABLE(Date,Sales) LOAD
STATE,
[08_31_2017],
[09_29_2017],
.....
FROM
.....
;
Thanks - any ideas do it automatically when new month end date is added to the file without modifying the script?
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.
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?
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.
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.
Thanks Petter,Vishwarath and Youssef.
Much appreciated.
You're welcome
Good luck