Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to load from a QVD only the records where the fiscal week is equal to max fiscal week.
I was wondering if someone would be able to point out the error in what I am doing:
Table1:
Load
Max(Fiscal_Wk) as MaxFW
Resident Table;
vWeek= MaxFW;
Table2:
Load *
Resident Table where Fiscal_Wk = $(vWeek);
//Store
Store Table2 into (Folder Location);
Drop Tables Table, Table1, Table2;
Thanks for your help!
Archie
LET vWeek = Peek('MaxFW', 0, 'Table1');
Table2:
Load *
Resident Table where Fiscal_Wk = '$(vWeek)';
Based on how I've done something similar in one of my reports...
Table1:
LOAD Max( Fiscal_Wk ) as MaxFW
RESIDENT Table;
LET vWeek = peek('MaxFW');
DROP TABLE Table1;
Table2:
LOAD *
RESIDENT Table
WHERE Fiscal_WK = $( vWeek );
// Drop tables etc...
Thanks for the quick response.
I seem to be getting an error that Table2 is not found.
Not sure why it doesn't recognize this table.
I used the same code:
Table1:
Load
Max(Fiscal_Wk) as MaxFW
Resident Table;
LET vWeek = Peek('MaxFW', 0, 'Table1');
Table2:
Load *
Resident Table where Fiscal_Wk = '$(vWeek)';
//Store
Store Table2 into (Folder Location)
Drop Tables Table, Table1, Table2;
Hey Archie,
Table is not found because its getting concatenated.
use
Table2:
Noconcatenate
Load *
Resident Table where Fiscal_Wk = '$(vWeek)';
Thanks
AJ
Thanks @Vivien TEXIER and @AjayVallab
Since Table and Table2 have the same exact fields, the tables were getting concatenated. You can prevent this by specifying the noconcatenate keyword. That forces Qlikview to create a separate table
Here is the final code
Table1:
Load
Max(Fiscal_Wk) as MaxFW
Resident Table;
LET vWeek = Peek('MaxFW', 0, 'Table1');
Table2:
noconcatenate
Load *
Resident Table where Fiscal_Wk = $(vWeek);
DROP Tables Table, Table1;
//Store
Store Table2 into (Folder location);
Drop Table Table2
//Drop Tables Table, Table1, Table2;
Hi Williams, You can get by Inner Join like below:
TABLE:
LOAD A, B, C, D, Fiscal_Wk From Source
Inner Join LOAD Max(Fiscal_Wk) AS Fiscal_Wk Resident TABLE ;