topic Re: Re: Rolling Ten days in QlikView

Rolling Ten days

suzel404 — Wed, 02 Apr 2014 19:11:48 GMT

Hi all,

How can I create a flag if I have more than 2 NBR on 10 rolling days, I flag 'KO' or flag 'OK'.

How can I do that into the script ?

Thanks for your help.

Re: Rolling Ten days

whiteline — Wed, 02 Apr 2014 19:33:39 GMT

Hi.

I calculated the rolling number of NBR, you can easily modify it to get 'OK':

Accum:

LOAD

Date,

NBR,

Date+IterNo()-1 as DateAccum

Resident Data

While IterNo()<=10;

left join (Data)

LOAD

DateAccum as Date,

Sum(NBR) as NBRAcc

Resident Accum

Group by DateAccum;

drop table Accum;

Re: Re: Rolling Ten days

suzel404 — Wed, 02 Apr 2014 21:22:01 GMT

Thanks whiteLine,

I used your code but the number NBR for Rolling 10 days is 3 and I get a strange result.

Re: Re: Rolling Ten days

suzel404 — Wed, 02 Apr 2014 21:59:31 GMT

I cannot obtain the number of NBR for 10 rollings days.

Data:
LOAD * INLINE [
Date, Store, NBR
01/01/2014, ST1, 0
02/01/2014, ST1, 0
03/01/2014, ST2, 1
04/01/2014 ,ST1, 0
05/01/2014 ,ST3, 0
06/01/2014 ,ST3, 0
07/01/2014 ,ST4, 0
08/01/2014 ,ST5, 1
09/01/2014 ,ST1, 0
10/01/2014 ,ST2, 1
11/01/2014 ,ST4, 1
12/01/2014 ,ST5, 1
13/01/2014 ,ST2, 1
14/01/2014 ,ST3, 1
15/01/2014 ,ST4, 1
16/01/2014 ,ST5, 1
17/01/2014 ,ST1, 1
18/01/2014 ,ST5, 1
19/01/2014 ,ST2, 1
20/01/2014 ,ST6, 1
21/01/2014 ,ST3, 1
22/01/2014 ,ST2, 1
23/01/2014 ,ST1, 1
24/01/2014 ,ST7, 1
25/01/2014 ,ST2, 1
26/01/2014 ,ST2, 1
27/01/2014 ,ST3, 1
28/01/2014 ,ST4, 1
29/01/2014 ,ST5, 1
30/01/2014 ,ST2, 1
31/01/2014 ,ST1, 1
];

AsOfDate:
LOAD
'Current' as Type,
Date as Date_AsOf,
Date as Date,
Store,
NBR
RESIDENT Data;

CONCATENATE (AsOfDate) LOAD
'Rolling 10 days' as Type,
Date as Date_AsOf,
Date+1-Iterno() as Date,
Store,
NBR
RESIDENT Data

WHILE IterNo() <= 10;

RIGHT JOIN LOAD DISTINCT
Date
RESIDENT Data;

DROP TABLE Data;

Re: Re: Re: Rolling Ten days

whiteline — Thu, 03 Apr 2014 05:40:54 GMT

Actually, you don't.

Since the DateAccum expression was Date+IterNo()-1 instead.

This trick saves you at least one join, since now you can build 'as of' calendar and load NBR values simultaneously.

You can add you flag with easy:

if(Sum(NBRAcc)=10, 'OK') as Flag

Re: Rolling Ten days

suzel404 — Thu, 03 Apr 2014 18:16:44 GMT

Many Thanks, Whiteline.