Variable in where clause

[daniel_f]

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

[swuehl]

Do

    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;

?

[mightyqlikers]

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.

[daniel_f]

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

[daniel_f]

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

[swuehl]

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

[Anil_Babu_Samineni]

Daniel,

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

[Anonymous · Accepted solution]

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);

[daniel_f]

hi,

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

thanks

daniel

[narayanamsn]

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)