Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

Cross table - adding columns


I have a spreadsheet that gains a new column once a week. Is't possible to make the cross table "dynamic"?


Spredsheet for sales

Product         2018-03-12          2018-03-19          2018-03-26

X                    50                         30                         40

Y                    30                         20                         20

Z                    20                         10                         30

and next week a new column: "2018-04-02" will be added to the spreadsheet.

How do i solve this whiteout editing the script?

Regards Sebastian

2 Replies
Contributor III

Re: Cross table - adding columns

Try like below

CrossTable(Date, Data)


(ooxml, embedded labels, table is Sheet1);

Contributor III

Re: Cross table - adding columns

Hi Sebastian,

I've done a similar solution where there were an unknown amount of columns that needed to be extracted. The below checks for the amount of columns and names them accordingly in a textstring (vL.Query) which is then called in the 'Temp' load. You should be able to reuse most of the below.





[$(vDataFolder)Herp_$(vCurrentYear)_from Derp.xlsx]

(ooxml, no labels, header is 3 lines, table is P1toP6Actual);

let vFields = NoOfFields('Temp1');

LET vL.ActColumnsCount = $(vFields) - 6;

LET vL.Query =;

LET i = 1;

Do while i<$(vL.ActColumnsCount)

LET vL.Query = chr($(i) + 65 + 6) & ' as [Act P' & $(i) & '], ' & '$(vL.Query)';

LET i = i + 1;


let vL.Query = Left(vL.Query,len(vL.Query)-2); //remove excess comma and blank space


noconcatenate LOAD

$(vCurrentYear) as YYYY,

A as [Herp Derp],

    B as Herp,

    C as Herp1,

    D as Derp,

    E as [Herpy Derpy],

    F as [Derpy Herpy],

    G as Derp1,


Resident Temp1;

drop table Temp1;

Community Browser