topic Re: Using a Variable when loading from QVD in QlikView

Using a Variable when loading from QVD

Tue, 18 Feb 2014 16:10:50 GMT

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

Re: Using a Variable when loading from QVD

vivientexier — Tue, 18 Feb 2014 16:13:38 GMT

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

Re: Using a Variable when loading from QVD

vivientexier — Tue, 18 Feb 2014 16:14:25 GMT

Table2:

Load *

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

Re: Using a Variable when loading from QVD

Tue, 18 Feb 2014 16:16:52 GMT

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

Re: Using a Variable when loading from QVD

Tue, 18 Feb 2014 16:18:53 GMT

try the example posted by Vivien TEXIER

it's the same answer that I had

Best Regards

Re: Using a Variable when loading from QVD

Tue, 18 Feb 2014 18:29:17 GMT

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;

Re: Using a Variable when loading from QVD

Tue, 18 Feb 2014 18:53:26 GMT

Hey Archie,

Table is not found because its getting concatenated.

use

Table2:

Noconcatenate

Load *

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

Thanks

Re: Using a Variable when loading from QVD

Tue, 18 Feb 2014 19:13:03 GMT

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;

Re: Using a Variable when loading from QVD

Tue, 18 Feb 2014 20:17:48 GMT

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 ;