Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

udaya_kumar
Valued Contributor

How to handle cross table for dynamic columns change

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.

NameLocation2019 Jan2019 Feb2019 Mar2019 Apr2019 May2019 Jun2019 Jul
AAbc1134416
BBcd7689067

 

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?

Labels (1)
1 Solution

Accepted Solutions
Ezir
Contributor II

Re: How to handle cross table for dynamic columns change

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

4 Replies
Ezir
Contributor II

Re: How to handle cross table for dynamic columns change

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
Valued Contributor

Re: How to handle cross table for dynamic columns change

Hi Ezir,

Thanks for your reply.

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

Syntax error

Unknown statement: *

Highlighted
Ezir
Contributor II

Re: How to handle cross table for dynamic columns change

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
Valued Contributor

Re: How to handle cross table for dynamic columns change

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