Only load data of last 4 weeks

[#1]

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

[Anonymous]

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

[#3]

Tried but failed. Is there any other way?

[#4]

Try this:

LET vWeekFrom = week(today());

LOAD week,

data,

data,

data,

data,

color

FROM table

where week>=$(vWeekFrom) and color='BLUE';

[er_mohit]

try this

where week(date(Today()))-4 and color='BLUE';

[#6]

you can try

week=week(date(Today()))-4

and color='BLUE';

[#7]

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?

[#8]

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