Dates as Columns

[evansabres]

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.

[swuehl]

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;

The Crosstable Load

[mgavidia]

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;

[oknotsen]

You beat me to it .

@ OP:

Go with Stefan's suggestion!

[evansabres]

Is it possible to do this for a loop script? I have data in the same format on several tabs.

[swuehl]

You can use a

For Each vTab in 'A','B','C'

  // Your multiple Table LOAD comes here

Next vTab

[evansabres]

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

[swuehl]

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

[evansabres]

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???

[swuehl]

As minor as vTest  used for file name can be, probably