Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
swati_rastogi27
Creator
Creator

Rolling related

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_DATEIDVOLUMENET_QTY
15-Jan-161100
16-Jan-161100
17-Jan-161100
18-Jan-161100
19-Jan-161100
20-Jan-161100
21-Jan-161100
22-Jan-161
23-Jan-161
24-Jan-161
25-Jan-16110
26-Jan-16110
27-Jan-16110
28-Jan-16110
29-Jan-16120010
30-Jan-16120010
31-Jan-16120010

Can anyone plz help?

6 Replies
swati_rastogi27
Creator
Creator

Any inputs , anyone?

vikasmahajan
Champion
Champion

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

Vikas

swati_rastogi27
Creator
Creator

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.

qliksus
Specialist II
Specialist II

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;

sunny_talwar

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?

infosense_devel
Creator II
Creator II

Go through This,

Its Property Of Expression,

For Data Rolling.