topic Re: Only load data of last 4 weeks in QlikView

Only load data of last 4 weeks

Tue, 09 Jul 2013 06:44:06 GMT

Hi,

I want to load data only from the last 4 weeks, I tried below but it's not working:

LOAD week,

data,

color

FROM table

where 'week'>=$(=Max(week))-3 and color='BLUE';

Anyone can help?

Peter

Re: Only load data of last 4 weeks

Anonymous — Tue, 09 Jul 2013 06:57:01 GMT

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

Re: Only load data of last 4 weeks

Tue, 09 Jul 2013 08:44:13 GMT

Tried but failed. Is there any other way?

Re: Only load data of last 4 weeks

Tue, 09 Jul 2013 08:52:38 GMT

Try this:

LET vWeekFrom = week(today());

LOAD week,

data,

color

FROM table

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

Re: Only load data of last 4 weeks

er_mohit — Tue, 09 Jul 2013 08:55:08 GMT

try this

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

Re: Only load data of last 4 weeks

Tue, 09 Jul 2013 09:07:35 GMT

you can try

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

and color='BLUE';

Re: Only load data of last 4 weeks

Wed, 10 Jul 2013 05:26:44 GMT

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?

Re: Only load data of last 4 weeks

Wed, 10 Jul 2013 08:57:26 GMT

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