Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
palanimurugan
New Contributor III

Re: carry forward values

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?

Re: carry forward values

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

palanimurugan
New Contributor III

Re: carry forward values

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
Highlighted

Re: carry forward values

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
New Contributor III

Re: carry forward values

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

Re: carry forward values

Please share your qvw.

palanimurugan
New Contributor III

Re: carry forward values

Hi Sunny..

PFA QVW..

Re: carry forward values

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
New Contributor III

Re: carry forward values

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!!