Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try like below
CrossTable(Date, Data)
LOAD * FROM
(ooxml, embedded labels, table is Sheet1);
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;