Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Only load data of last 4 weeks

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

Tags (1)
7 Replies
jonbrough
Valued Contributor

Re: Only load data of last 4 weeks

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

Not applicable

Re: Only load data of last 4 weeks

Tried but failed. Is there any other way?

Not applicable

Re: Only load data of last 4 weeks

Try this:

LET vWeekFrom = week(today());

LOAD week,

data,

data,

data,

data,

color

FROM table

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


er_mohit
Honored Contributor II

Re: Only load data of last 4 weeks

try this

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

Not applicable

Re: Only load data of last 4 weeks

you can try

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

and color='BLUE';

Not applicable

Re: Only load data of last 4 weeks

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?

Not applicable

Re: Only load data of last 4 weeks

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

Community Browser