topic Cross table Conversion with incremental columns in QlikView

Cross table Conversion with incremental columns

shruthibk — Wed, 25 Nov 2020 16:16:04 GMT

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

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

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 — Wed, 25 May 2016 07:40:22 GMT

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 — Wed, 25 May 2016 08:10:25 GMT

Thank You