topic Re: Problem choosing weeks in the script in QlikView

Problem choosing weeks in the script

Mon, 13 Apr 2015 17:25:04 GMT

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

Re: Problem choosing weeks in the script

sunny_talwar — Mon, 13 Apr 2015 17:40:10 GMT

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

Re: Problem choosing weeks in the script

Mon, 13 Apr 2015 17:49:03 GMT

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.

Re: Problem choosing weeks in the script

sunny_talwar — Mon, 13 Apr 2015 18:02:29 GMT

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

Re: Problem choosing weeks in the script

sunny_talwar — Mon, 13 Apr 2015 18:13:33 GMT

See if this is what you wanted? (PFA)

Best,

Sunny

Re: Problem choosing weeks in the script

Mon, 13 Apr 2015 18:48:45 GMT

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

Re: Problem choosing weeks in the script

sunny_talwar — Mon, 13 Apr 2015 19:04:28 GMT

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