Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
palanimurugan
Contributor III
Contributor III

carry forward values

Dear Experts,

I need to carry forward the value, if the value is zero.

Attached Snapshot for your reference.

For  Example, In third row the value has Zero.So I have to bring and set the previous values(i.e 45).

Similarly i have set for all values. Kindly help me to fix this.

Thanks!!!

   

iddateValue
16/4/201445
210/3/20140
310/16/20144
1211/7/20142
211/13/201423
1212/29/20144
11/12/20150
12/26/20152
123/19/20150
34/14/20150
24/20/20150
15/22/20153
237/17/20151
18 Replies
palanimurugan
Contributor III
Contributor III
Author

Hi Sunny,

I have tried with different data set, here the out is different.

Table:

LOAD * INLINE [

id,Value

0001SA-1,0

0001SA-1,553

0001SA-1,886

0001SA-1,946

0001SA-1,963

0001SA-1,0

0001SA-1,1002

0001SA-1,0

0001SA-1,1083

0001SA-2,613

0001SA-2,643

0001SA-2,672

0001SA-2,0

0001SA-2,729

0001SA-2,730

0001SA-2,733

0001SA-2,785

0001SA-3,0

0001SA-3,214

0001SA-3,222

0001SA-3,0

0001SA-3,249

0001SA-3,0

0001SA-3,264

0001SA-3,266

0001SA-3,300

0001SA-3,470

0001SA-3,553

0001SA-4,0

0001SA-4,211

0001SA-4,0

0001SA-4,0

0001SA-4,216

0001SA-4,218

0001SA-5,0-0

0001SA-5,243

0001SA-5,253

0001SA-5,258

0001SA-5,261

0001SA-5,268

0001SA-5,298

0001SA-5,307

0001SA-5,0

0001SA-5,599

0001SA-5,664

];


FinalTable:

LOAD id,

Value,

If(Value = 0, Peek('NewValue'), Value) as NewValue

Resident Table

Order By id;

DROP Table Table;


Could you please try your end and suggest?

sunny_talwar

What is the expected output here? and order by id? not by date or any other field?

palanimurugan
Contributor III
Contributor III
Author

Dear Sunny,

Sorry for the confusion, Below are my actual data. Here I need  to show carry forwarded values based on the id.

The issue is i can able to carry forward the values, but not based on the id.

If I filter the id getting previous id value as carry forward value (Actually I need not to bring previous id value to next id). Please suggest how to fix this.

note: please apply carry forward for 0 and '0-0'

    

idStart DateEnd DateValues
0001IA-12014-02-184/2/20150-0
0001IA-12014-11-251/21/2016553
0001IA-12015-06-036/23/2016886
0001IA-22015-03-205/23/2016946
0001IA-22015-10-1311/10/2016963
0001IA-22015-02-049/22/20160-0
0001IA-32015-09-2511/10/20161002
0001IA-32014-10-2111/9/20151062
0001IA-32015-09-299/29/20161083
0001IA-42015-06-186/27/2016613
0001IA-42015-05-215/23/2016643
0001IA-42014-02-256/1/2015672
0001IA-42015-12-0212/5/20160
0001IA-52015-11-1812/22/2016729
0001IA-52014-04-254/27/2015730
0001IA-52015-01-286/20/2016733
0001IA-62014-08-2710/1/2015785
0001IA-62014-05-076/25/20150
0001IA-62015-06-039/5/2016214
0001IA-72014-09-2410/1/2015222
0001IA-72014-04-034/30/20150
0001IA-72014-07-047/23/2015249
0001IA-72015-05-266/9/2016254
0001IA-72016-01-192/16/2017264
0001IA-82016-06-036/8/2017266
0001IA-82014-06-139/3/2015300
0001IA-82014-09-2310/26/2015470
0001IA-82014-04-284/30/2015553
0001IA-92015-05-266/9/20160-0
0001IA-92015-06-036/9/2016211
0001IA-92014-03-1110/12/20150-0
0001IA-92014-07-179/28/2015215
0001IA-102015-05-056/27/2016216
0001IA-102015-04-026/6/2016218
0001IA-102016-04-244/24/20170-0
0001IA-102015-10-2212/28/2016243
0001IA-112015-01-163/9/2016253
0001IA-112015-03-106/6/2016258
0001IA-112016-02-263/20/2017261
0001IA-122014-08-1111/30/2015268
0001IA-122014-03-2012/2/2015298
0001IA-122014-10-0312/9/2015307
0001IA-132014-10-3012/21/2015455
0001IA-132016-02-243/13/2017599
sunny_talwar

I guess add a second condition

If(id = Peek(id) and Value = 0, Peek('NewValue'), Value) as NewValue

and sort by first id and then Date

Order By id, Date;

palanimurugan
Contributor III
Contributor III
Author

Sunny,

This is also not proving expected result..

Below is the used expression

Tab_final:

Load id,[Start Date], [End Date], Values,

If(id = Peek(id) and Value = 0 or Value ='0-0', Peek('NewValue'), Value) as NewValue

Resident Tab1

order by id,[Start Date], [End Date];

The output getting like belowResult.png

sunny_talwar

Please share your qvw.

palanimurugan
Contributor III
Contributor III
Author

Hi Sunny..

PFA QVW..

sunny_talwar

I guess the code is working, but may be not as your expectation... what would you want to see here?

Capture.PNG

Since the first Start Date is 0 and there is no value before it, what can it use for NewValue? If you decide to pick the next value which is 785... then what happens here

Capture.PNG

613 will be 0 because there is no value after the last Start Date

palanimurugan
Contributor III
Contributor III
Author

Sunny,

Great!!

As per your explanation, this is correct. I am also expecting same result.

But what I did in previous, added only 3 fields (i.e id and value and Newvalue) and expecting above result.

The mistake is from my end. sorry for the inconvenience.

Thanks!!