topic Re: Rolling related in QlikView

Rolling related

swati_rastogi27 — Thu, 09 Jun 2016 06:33:52 GMT

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?

Re: Rolling related

swati_rastogi27 — Fri, 10 Jun 2016 04:50:16 GMT

Any inputs , anyone?

Re: Rolling related

vikasmahajan — Fri, 10 Jun 2016 05:57:11 GMT

Your requirement is not clear , what you want to achieve is not clear . Please provide more information

Vikas

Re: Rolling related

swati_rastogi27 — Fri, 10 Jun 2016 07:13:56 GMT

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.

Re: Rolling related

qliksus — Fri, 10 Jun 2016 09:16:43 GMT

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;

Re: Rolling related

sunny_talwar — Fri, 10 Jun 2016 09:48:38 GMT

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?

Re: Rolling related

infosense_devel — Fri, 10 Jun 2016 15:07:36 GMT

Go through This,

Its Property Of Expression,

For Data Rolling.