Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Hi Ezir,
Thanks for your reply.
I get an error while using only Load * from table statement with crosstable.
Syntax error
Unknown statement: *
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
The first method actually worked, there was some comment issue which created problem.