topic Re: How to handle cross table for dynamic columns change in QlikView

How to handle cross table for dynamic columns change

udaya_kumar — Sat, 16 Nov 2024 02:40:06 GMT

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?

Re: How to handle cross table for dynamic columns change

Ezirraffner — Thu, 22 Aug 2019 11:29:12 GMT

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

Re: How to handle cross table for dynamic columns change

udaya_kumar — Thu, 22 Aug 2019 11:35:06 GMT

Hi Ezir,

Thanks for your reply.

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

Syntax error

Unknown statement: *

Re: How to handle cross table for dynamic columns change

Ezirraffner — Thu, 22 Aug 2019 11:51:35 GMT

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

Re: How to handle cross table for dynamic columns change

udaya_kumar — Thu, 22 Aug 2019 12:35:28 GMT

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