Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello -
My question concerns having dates (Months) as Field Names and the problem that presents.
My data appears as this:
Brand Seat Level Value Jan Feb Mar Apr May Jun Jul Aug Sep Aug Sep Oct Nov Dec
Name 100 Level 100 11 24 99 88 6 44 11 2 9 6 33 100 999 9
Name 200 Level
Name 300 Level
This is how the data enters from my data source. I want to have the months as a dimension as opposed to each month being the name of a column.
Is there a work around for this?
Thank you.
Use CROSSTABLE LOAD prefix:
CROSSTABLE (Month, MonthValue,3)
LOAD Brand, [Seat Level], Value, Jan, Feb , Mar, Apr, May , Jun, Jul, Aug, Sep, Oct, Nov, Dec
FROM YourTable;
I believe you need to apply the Crosstable prefix to the Load or Select statement tocapture the data as you want it.
For example:
Crosstable(Month, Sales)
Load *
from example.xls;
You beat me to it .
@ OP:
Go with Stefan's suggestion!
Is it possible to do this for a loop script? I have data in the same format on several tabs.
You can use a
For Each vTab in 'A','B','C'
// Your multiple Table LOAD comes here
Next vTab
Sorry to be a bother, but can you peek at my script, I get an error message of having conflicting prefixes:
DIRECTORY 'C:\Users\ebarrick\Desktop\LoyaltySim';
LET vStartSheetNum = 1;
LET vEndSHeetNum = 4;
Let vExcelFileName = 'Test';
STH:
LOAD
'' AS TEST1
AUTOGENERATE (0);
FOR index = vStartSheetNum TO vEndSheetNum
CONCATENATE(STH)
CROSSTABLE(Month, MonthValue,3)
LOAD Brand, [Relationship Bucket], priceLVL, STHValue, January, February, March, April, May, June, July, August, September, October, November, December
FROM [$(vExcelFileName).xlsx] (ooxml, embedded labels, table is [rel $(index)]);
NEXT
Does this work?
DIRECTORY 'C:\Users\ebarrick\Desktop\LoyaltySim';
LET vStartSheetNum = 1;
LET vEndSHeetNum = 4;
Let vExcelFileName = 'Test';
FOR index = vStartSheetNum TO vEndSheetNum
STH:
CROSSTABLE(Month, MonthValue,3)
LOAD Brand, [Relationship Bucket], priceLVL, STHValue, January, February, March, April, May, June, July, August, September, October, November, December
FROM [$(vExcelFileName).xlsx] (ooxml, embedded labels, table is [rel $(index)]);
NEXT
If your CROSSTABLE created field names are identical, the tables should get auto-concatenated
I get the error of
Cannot open file 'C:\Users\ebarrick\Desktop\LoyaltySim\.xlsx' The system cannot find the file specified.
STH:
CROSSTABLE(Month, MonthValue3)
LOAD Brand, [Relationship Bucket], priceLVL, STHValue, January, February, March, April, May, June, July, August, September, October, November, December
FROM [.xlsx] (ooxml, embedded labels, table is [rel 1])
I have my script set up as:
DIRECTORY 'C:\Users\ebarrick\Desktop\LoyaltySim';
LET vStartSheetNum = 1;
LET vEndSHeetNum = 4;
Let vExcelFileName = 'Test';
FOR index = vStartSheetNum to vEndSheetNum
STH:
CROSSTABLE(Month, MonthValue3)
LOAD Brand, [Relationship Bucket], priceLVL, STHValue, January, February, March, April, May, June, July, August, September, October, November, December
FROM [$(vTest).xlsx] (ooxml, embedded labels, table is [rel $(index)]);
NEXT
Am i missing something minor???
As minor as vTest used for file name can be, probably