Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have a complex scenario for rolling data.
Earlier we used to roll by picking up value from previous row, so for example , if I got 100/- as my value on 15th Jan and no data thereafter, I rolled it till date.
Now the requirement is to roll only for 7 days and then stop rolling.
Considering, I am looking at data values from 15th - 31st Jan
For Example ,
ID=1
DATA_DATE=15-JAN-2016
VOLUME=100
NET_QTY=10
ID=1
DATA_DATE=25-JAN-2016
VOLUME=NULL
NET_QTY=5
ID=1
DATA_DATE=29-JAN-2016
VOLUME=200
NET_QTY=NULL
My Rolled data should look like :
DATA_DATE | ID | VOLUME | NET_QTY |
15-Jan-16 | 1 | 100 | |
16-Jan-16 | 1 | 100 | |
17-Jan-16 | 1 | 100 | |
18-Jan-16 | 1 | 100 | |
19-Jan-16 | 1 | 100 | |
20-Jan-16 | 1 | 100 | |
21-Jan-16 | 1 | 100 | |
22-Jan-16 | 1 | ||
23-Jan-16 | 1 | ||
24-Jan-16 | 1 | ||
25-Jan-16 | 1 | 10 | |
26-Jan-16 | 1 | 10 | |
27-Jan-16 | 1 | 10 | |
28-Jan-16 | 1 | 10 | |
29-Jan-16 | 1 | 200 | 10 |
30-Jan-16 | 1 | 200 | 10 |
31-Jan-16 | 1 | 200 | 10 |
Can anyone plz help?
Any inputs , anyone?
Your requirement is not clear , what you want to achieve is not clear . Please provide more information
Vikas
I want to roll data , but not indefinitely , it should be rolled only for a max period of 7 days.
For example , I receive records daily , on 15th Jan 2016 , I receive VOLUME=1000.
for the next 20 days I get this value as NULL in my data.
Rolling for 7 days means , till 22nd Jan 2016, VOLUME would be populated as 1000, after 22nd Jan, I should get it remain as NULL.
After 20 days , again I get VOLUME=2000 . Rolling will start as mentioned above.
Check if this Helps
Script
A:
LOAD * INLINE [
ID, Date, Volume, Qty
1,15/05/2016,100,10
1,25/05/2016,,5
1,29/05/2016,200,
];
Join(A)
load
ID,
Date ,
if( not isnull(Previous(Date)) and ID = Previous(ID) ,Previous(Date), date(Date+1)) as Todate
Resident A
Order by Date desc;
Final:
load
ID,
Date,
date(Date+IterNo()-1) as DateGen,
Volume,
Qty,
if( date(Date+IterNo()-1) <= date(Date+7),Volume) as NVolume,
if( date(Date+IterNo()-1) <= date(Date+7),Qty) as NQty
Resident A
While date(Date+IterNo()-1) < date(Todate)
;
DROP Table A;
This is needed in the script or front end? Also it seems that above is the expected output you want to see, would be able to share the raw data behind the output?
Go through This,
Its Property Of Expression,
For Data Rolling.