Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to load data only from the last 4 weeks, I tried below but it's not working:
LOAD week,
data,
data,
data,
data,
color
FROM table
where 'week'>=$(=Max(week))-3 and color='BLUE';
Anyone can help?
Peter
The single quotes around the week record name don't look right. Try:
WHERE week >= $(=Max(week))-3
AND colour = 'BLUE';
If that doesn't work, a simpler clause would be:
WHERE week >= week(today())-3
AND colour = 'BLUE';
If you want to see the last 4 weeks and you don't think data will run up until today, you may need to include the max(week) in the data table, using a GROUP BY clause:
1. Include a field when you load in your table that has one value for all the records
TheTable:
LOAD
1 AS SameField,
week,
data....
2. Left Join onto The Table as follows:
LEFT JOIN (TheTable) LOAD
SameField,
max(week) AS MaxWeek
RESIDENT TheTable
GROUP BY SameField
3. You can then use MaxWeek-3 without the dollar expansion as it is a data table field
Sorry for not being able to try this out. I'm looking at the forum while doing a reload.
Jonathan
Tried but failed. Is there any other way?
Try this:
LET vWeekFrom = week(today());
LOAD week,
data,
data,
data,
data,
color
FROM table
where week>=$(vWeekFrom) and color='BLUE';
try this
where week(date(Today()))-4 and color='BLUE';
you can try
week=week(date(Today()))-4
and color='BLUE';
Thanks but the situation is maybe more complicated than I thought. Actually we're using "Year Week" here instead of "Week". e.g. 201301 means wk01 of 2013.
If this week is 201301, I would like to only load data since 201250, is it possible to make a formula to achieve this?
Then you might find useful a sequential week number.
Create the vWeekFrom using a sequential wwek number so won't have problems with year ends.
let vWeekFrom = (year(today()) * 12 + week(today()) ) - 7;
Then, use a WHERE clause in your LOAD statement:
LOAD
...
WHERE num(month(datefield)) * 12 + week(datefield) >= $(vWeekFrom);