How to handle cross table for dynamic columns change

[udaya_kumar]

Hi,

I have an excel file which gets updated with the latest months and future months data.

So when we move to next month, the future 12months data months will be updated as the column header in the excel.

So when we use the cross table in the script, we won't get the new future months updated as columns in the excel into the script to reload.

Name	Location	2019 Jan	2019 Feb	2019 Mar	2019 Apr	2019 May	2019 Jun	2019 Jul
A	Abc	1	1	3	4	4	1	6
B	Bcd	7	6	8	9	0	6	7

When we have data like this, when we move the next few months like 2019 Aug, 2019 Sep, 2019 Oct,.... and so on in the excel data. How do we handle this situation of getting the new columns (Months) into script in QlikView?

[Ezir · Accepted solution]

Hi @udaya_kumar ,

You can try:

[SheetSales]:
CROSSTABLE (YearMonth,Sales, 2)
LOAD *
 FROM [lib://Documents/PIVOTABLE.xlsx]
(ooxml, embedded labels, table is SheetSales);

🤔

Best regards,

Ezir

[udaya_kumar]

Hi Ezir,

Thanks for your reply.

I get an error while using only Load * from table statement with crosstable.

Syntax error

Unknown statement: *

[Ezir]

Hi Udaya,

An example filtering future months... 

cross:
CROSSTABLE (YearMonth,Sales, 2)
load * inline [Name,	Location,	2019 Jan,	2019 Feb,	2019 Mar,	2019 Apr,	2019 May,	2019 Jun,	2019 Jul,	2019 Aug,	2019 Sep,
A,	Abc,	1,	1,	3,	4,	4,	1,	6,	1,	1,
B,	Bcd,	7,	6,	8,	9,	0,	6,	7,	1,	1,
C,	EQAS,	2,	3,	9,	7,	0,	1,	3,	1,	1,
];

noconcatenate

sheetFilter:
load *
resident cross
where Date#(YearMonth,'YYYY MMM')>Date(today(),'YYYY MMM');

drop table cross;

Please attach an example of your Excel spreadsheet.

Ezir

[udaya_kumar]

The first method actually worked, there was some comment issue which created problem.