topic Re: Dates as Columns in QlikView

Dates as Columns

evansabres — Thu, 04 Feb 2016 22:16:44 GMT

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.

Re: Dates as Columns

swuehl — Thu, 04 Feb 2016 22:25:13 GMT

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

Re: Dates as Columns

mgavidia — Thu, 04 Feb 2016 22:27:08 GMT

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;

Re: Dates as Columns

oknotsen — Thu, 04 Feb 2016 22:30:07 GMT

You beat me to it .

@ OP:

Go with Stefan's suggestion!

Re: Dates as Columns

evansabres — Fri, 05 Feb 2016 18:56:44 GMT

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

Re: Dates as Columns

swuehl — Fri, 05 Feb 2016 19:06:04 GMT

You can use a

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

// Your multiple Table LOAD comes here

Next vTab

Re: Dates as Columns

evansabres — Fri, 05 Feb 2016 19:08:12 GMT

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

Re: Dates as Columns

swuehl — Fri, 05 Feb 2016 19:17:44 GMT

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

Re: Dates as Columns

evansabres — Fri, 05 Feb 2016 19:32:32 GMT

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

Re: Dates as Columns

swuehl — Fri, 05 Feb 2016 19:34:21 GMT

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

Re: Dates as Columns

evansabres — Fri, 05 Feb 2016 19:52:13 GMT

That was the issue, thank you. So now, the script works, however, when I re-load the application, 'Brand' is now part of months. (January - December) Any thoughts on why that would be occurring?

Re: Dates as Columns

swuehl — Fri, 05 Feb 2016 20:14:30 GMT

Your are using four qualifying fields,

CROSSTABLE(Month, MonthValue, 4)

Re: Dates as Columns

MarcoWedel — Fri, 05 Feb 2016 20:19:59 GMT

‌crosstable creates text values as months.

you might have to left join "real " months afterward.

regards

Marco