Problem choosing weeks in the script

[#1]

Hi

I have attached a sample qvd and qvw.

Basically I have an open week field which contains data for 3 years. I am trying to bring in data of only 16 latest weeks(available in the db) and its corresponding weeks from last year(same week number but year -1).

The result set should be

Open week dim

11/21/2014

11/28/2014

12/05/2014

.

.

.

.

.

.

03/06/2015

and open week previous year

11/15/2013

11/22/2013

11/29/2013

....

.

.

.

03/07/2014

Why I am trying to do this in the script ?? because the size of the app is too big and I want to reduce it to contain only the relevant data.

I have tried lot of techniques but it keeps giving me script error.

Please help me in understanding how to reduce based on the data given.(also I would prefer if no hardcoding was done as this app needs to work for next year also.)

Thank you

[sunny_talwar]

Can you post your script here, instead of looking at a 85MB file it might be easier to look at the portion of the script where you are facing the problem.

HTH

Best,

Sunny

[#3]

Fact:

LOAD 

     [Open Week Num],

     [Open Week]

FROM

[\openweek.qvd]

(qvd);

Fact2:

Load

[Open Week] as [Open Week Dim],

[Open Week Num] as [Open Week Num Dim]

Resident Fact

where floor(num([Open Week]))>floor((num(max([Open Week]))))-112);

is one of the examples i tried..but no matter what where condition I put i keep getting script error.

[sunny_talwar · Accepted solution]

This won't work like the way you have it in there. Try this instead:

Fact:

LOAD

     [Open Week Num],

     [Open Week]

FROM

[\openweek.qvd]

(qvd);

MaxOpenWeek:

LOAD Max([Open Week]) as MaxOpenWeek

Resident Fact;

LET vMaxOpenWeek = Peek('MaxOpenWeek');

Fact2:

Load

[Open Week] as [Open Week Dim],

[Open Week Num] as [Open Week Num Dim]

Resident Fact

where [Open Week]>($(vMaxOpenWeek)-112);

DROP Tables Fact, MaxOpenWeek;

Give this a shot.

Best,

Sunny

[sunny_talwar]

See if this is what you wanted? (PFA)

Best,

Sunny

[#6]

This worked perfectly, If you do not mind can you explain why you did what you did (so that i have an insight on how qlikview functions) and also what do you think the expression for last year same week number should be??

Thanks for all your help

[sunny_talwar]

I may be wrong, but this is what I understand:

When you have this where statement

where floor(num([Open Week]))>floor((num(max([Open Week]))))-112);

QlikView checks it for each row that [Open Week] > Max[Open Week]-112 which is always going to be true for any given row because (Example: [Open Week] = 01/01/2015 will always be greater than 01/01/2015 - 112 days)

When I used the Peek function, Max([Open Week]) is not a field value, it changes into a static value and now I can compare a static value to a field.

I hope what I said make sense.

Best,

Sunny