Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to select last 20 weeks from my base data using where /match condition. I have a field Week_ID, ranges between 1 to 52 for an year. I want select latest 20 weeks. I am trying to write something below. but getting error:
where Count(WEEK_ID)<=52 order by WEEK_ID desc;
Regards, please suggest.
source:
load *inline [
Week, Sales
1, 100
2, 90
3, 80
4, 110
5, 120
6, 90
7, 80
];
max: load max(Week) as MaxWeek Resident source;
let vMaxWeek=peek('MaxWeek');
final: NoConcatenate load * Resident source where Week >= $(vMaxWeek)-2;
DROP Table source;
View solution in original post
WEEK_ID > 32
Try this.... WHERE WEEK_ID > 32 ORDER BY WEEK_ID desc;
Just need to use
Where WEEK_ID >= 33;
I am not sure why you need to order WEEK_ID, unless you want to do it for some other reason not mentioned here.
HTH
Best,
S
if your WEEK_ID is the number of the week, you don't need count() function and for that matter why do you need to check if it is <= 52?
to select the last 20 weeks, you need something like below:
data1:
Load *
from /*your table*/
Order by WEEK_ID desc;
data2:
noconcatenate First 20 //this selects the latest 20 weeks
load * resident data1;
drop table data1;
Hi, the issue is not resolved. Let me restructure it.
Suppose this is the data, how can I upload only last 3 weeks data in QVW?
Thanks!
First 20 will load the first 20 rows of data.
This is unlikely to be the last 20 weeks of data!
Good point I need last 3 weeks , not last 3 rows
Indeed. It will work correctly only if each data row represents exactly one week.
It is safer to have Date in the data (it could be a week start for example), so it can be compared with date(today() - 7 * 20)
Two ways:
Method 1:
Table:
LOAD * INLINE [
Table1:
NoConcatenate
LOAD *
Resident Table
Where Week >= 5;
DROP Table Table;
Method 2:
First 3
Order By Week desc;
