Cross table - adding columns

[sebastian_fager]

Hi,

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

Example:

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

[ChennaiahNallani]

Try like below

CrossTable(Date, Data)

LOAD * FROM

(ooxml, embedded labels, table is Sheet1);

[niclaz79]

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.

Temp1:

LOAD

           *

FROM

[$(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;

Loop

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

Temp:

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,

    $(vL.Query)

Resident Temp1;

drop table Temp1;