topic Variable in where clause in QlikView

Variable in where clause

daniel_f — Mon, 20 Jun 2016 09:20:56 GMT

Hi All,

I'm trying to load a table with filter in Where clause position and i need your help

how can i load only quarter 2016-Q3 if this is my script

The current "Quarter_Rank" is 38 and i need to load next Next_Quarter_Rank

Table:

LOAD

[Product 1],

[Product 2],

[Total Qty],

[Year Quarter], // format: '2016-Q2' (list of quarters from 2013-Q1 to 2017-Q4)

Quarter_Rank, // numbers 1 to 100, each quarter have a number

Next_Quarter_Rank, // is 39 (number)

Actual_Quarter_Rank // is 38 (number)

FROM

[..\QVD\Test\FC_Quarter.qvd]

(qvd)

Where Quarter_Rank = / * i need a variable here to load Quarter_Rank= 39 * / ;

i tried this kind:

Where Next_Quarter_Rank = Actual_Quarter_Rank + 1 but doesn't work (load empty)

can someone advise the right method?

Thanks in advance,

Daniel

Re: Variable in where clause

swuehl — Mon, 20 Jun 2016 09:33:24 GMT

Next_Quarter_Rank, // is 39 (number)

Actual_Quarter_Rank // is 38 (number)

show a constant value for each record in your input table?

Then

Where Next_Quarter_Rank = Actual_Quarter_Rank + 1;

should return true and you should see all records.

But that's probably also not the filter you want to achieve, right?

Maybe

Where Quarter_Rank = Actual_Quarter_Rank + 1;

Re: Variable in where clause

mightyqlikers — Mon, 20 Jun 2016 09:37:03 GMT

Hi Daniel,

try below script.

tmp:

load

max(Next_Quarter_Rank) as Next_Quarter_Rank

FROM

[..\QVD\Test\FC_Quarter.qvd]

(qvd) where [Year Quarter]='2016-Q3';

let vQtrNum = peek('Next_Quarter_Rank');

drop table tmp;

Table:

LOAD

[Product 1],

[Product 2],

[Total Qty],

[Year Quarter], // format: '2016-Q2' (list of quarters from 2013-Q1 to 2017-Q4)

Quarter_Rank, // numbers 1 to 100, each quarter have a number

Next_Quarter_Rank, // is 39 (number)

Actual_Quarter_Rank // is 38 (number)

FROM

[..\QVD\Test\FC_Quarter.qvd]

(qvd) where Quarter_Rank=$(vQtrNum);

$@M.

Re: Variable in where clause

daniel_f — Mon, 20 Jun 2016 11:26:20 GMT

hi SAM,

thanks for your quickly respond, but the problem with this script is that is not automatically

"where [Year Quarter]='2016-Q3';" is manually

since i know what the actual quarter rank is, i want to create some automatically load process

any other idea?

thanks

Daniel

Re: Variable in where clause

daniel_f — Mon, 20 Jun 2016 11:37:01 GMT

Hi Swuehl,

i need something like: where Quarter_Rank = Next_Quarter_Rank

your sample 1: Where Next_Quarter_Rank = Actual_Quarter_Rank + 1; show me the current quarter (not the next)

your sample 2:Where Quarter_Rank = Actual_Quarter_Rank + 1; doesn't work

any other idea?

thanks

Daniel

Re: Variable in where clause

swuehl — Mon, 20 Jun 2016 13:19:58 GMT

Lot of ideas, but without knowing how your input table records actually look like, it does not make sense to guess.

Re: Variable in where clause

Anil_Babu_Samineni — Mon, 20 Jun 2016 13:38:58 GMT

Daniel,

Can you please post sample Application with Expect O/P. As Swuehl, How do we get the Correct?

Re: Variable in where clause

Anonymous — Mon, 20 Jun 2016 14:31:00 GMT

Instead of the hard coded you can use something like that:

LET next_q = Year(AddMonths(Date(Now()),4))&'-Q'&Ceil(Month(AddMonths(Date(Now()),4))/4);

Re: Variable in where clause

daniel_f — Wed, 22 Jun 2016 05:55:29 GMT

hi,

i added the qvw in order to try to get a solution

thanks

daniel

Re: Variable in where clause

narayanamsn — Wed, 22 Jun 2016 07:03:31 GMT

Hi Daniel,

This may be helpful while uploading the data from QVD..

where YearQuarter = year(Date(now()))&'-Q'&(Ceil(Num(Month(Date(now())))/3)+1)..

I am assuming each quarter has only one rank. This script will upload the data for next quarter ..but this has a limitation on when you are reloading the script..

Now it shows 2016-Q3 data as the we are in Jun(Q2)

Re: Variable in where clause

daniel_f — Wed, 22 Jun 2016 08:34:22 GMT

Can this file help?

Re: Variable in where clause

daniel_f — Wed, 22 Jun 2016 08:50:17 GMT

Hi Satya,

thank for your help

You assuming right, each quarter have only one rank.

well, the script is working but i don't understand what the limitation is when i reload the script.

additional, the formula is not generic. for example "year(Date(now()))" will not be valid when i'll be on 2016-Q4

any other idea

Re: Variable in where clause

Anil_Babu_Samineni — Wed, 22 Jun 2016 09:40:58 GMT

Daniel,

How about Dense_Rank() in DB?

Re: Variable in where clause

Anonymous — Wed, 22 Jun 2016 09:46:34 GMT

My calculation is generic:

I tried it with the following two dates and it worked perfectly giving 2017-Q1

LET YearQuarter_1 = Year(AddMonths(Date('10/01/2016'),4))&'-Q'&Ceil(Month(AddMonths(Date('10/01/2016'),4))/4);

LET YearQuarter_2 = Year(AddMonths(Date('12/31/2016'),4))&'-Q'&Ceil(Month(AddMonths(Date('12/31/2016'),4))/4);

trace $(YearQuarter_1) $(YearQuarter_2);

Re: Variable in where clause

daniel_f — Wed, 22 Jun 2016 11:09:09 GMT

can you please explain?