Cross table Conversion with incremental columns

[shruthibk]

Hi all,

I got requirement to transpose an  excel file  but my concern is going forward columns will be incremented how can i automate it.

for example

id

Name

Status

Assessment Rating

Assessment Rating Quarter

2015Q4

2016Q1

2016Q2

2016Q3

i have transposed using cross table like this

CrossTable(YearQuarter, Rating, 5)

LOAD [ Id],

     [ Name],

     [ Status],

     Assessment Rating],

     [Assessment Rating Quarter],

2015Q4,

2016Q1,

2016Q2,

2016Q3

from

going forward i ll be getting data like

id

Name

Status

Assessment Rating

Assessment Rating Quarter

2015Q4

2016Q1

2016Q2

2016Q3

2016Q4

2017Q1

how can i automate this to get new columns to transpose otherewise i have to manually add everytime

Thanks ,

Shruthi

[Gysbert_Wassenaar · Accepted solution]

In this case it's easier to use the * (star) instead of an explicit field list. That way extra Quarter columns don't require a change in the script.

CrossTable(YearQuarter, Rating, 5)

LOAD * FROM ..

Note: You do have to make sure the first five fields are also always the first five columns in the excel file.

[shruthibk]

Thank You