Using a Variable when loading from QVD

[#1]

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

[vivientexier]

LET vWeek = Peek('MaxFW', 0, 'Table1');

[vivientexier]

Table2:

Load *

Resident Table where Fiscal_Wk = '$(vWeek)';

[#4]

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...

[#5]

try the example posted by Vivien TEXIER

it's the same answer that I had

Best Regards

[#6]

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;

[#7]

Hey Archie,

Table is not found because its getting concatenated.

use

Table2:

Noconcatenate

Load *

Resident Table where Fiscal_Wk =  '$(vWeek)';

Thanks

AJ

[#8]

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;

[#9]

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 ;