Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Using a Variable when loading from QVD

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

8 Replies
vivientexier
Contributor II

Re: Using a Variable when loading from QVD

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

vivientexier
Contributor II

Re: Using a Variable when loading from QVD

Table2:

Load *

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

Not applicable

Re: Using a Variable when loading from QVD

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

Not applicable

Re: Using a Variable when loading from QVD

try the example posted by Vivien TEXIER

it's the same answer that I had

Best Regards

Not applicable

Re: Using a Variable when loading from QVD

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;

Not applicable

Re: Using a Variable when loading from QVD

Hey Archie,

Table is not found because its getting concatenated.

use

Table2:

Noconcatenate

Load *

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

Thanks

AJ

Not applicable

Re: Using a Variable when loading from QVD

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;

Not applicable

Re: Using a Variable when loading from QVD

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 ;