Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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;
?
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.
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
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
Lot of ideas, but without knowing how your input table records actually look like, it does not make sense to guess.
Daniel,
Can you please post sample Application with Expect O/P. As Swuehl, How do we get the Correct?
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);
hi,
i added the qvw in order to try to get a solution
thanks
daniel
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)