Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
sebastian_fager
Contributor III
Contributor III

Cross table - adding columns

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

2 Replies
ChennaiahNallani
Creator III
Creator III

Try like below

CrossTable(Date, Data)

LOAD * FROM

(ooxml, embedded labels, table is Sheet1);

niclaz79
Partner - Creator III
Partner - Creator III

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;