Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
Thanks whiteLine,
I used your code but the number NBR for Rolling 10 days is 3 and I get a strange result.
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;
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
Many Thanks, Whiteline.