carry forward values

[palanimurugan]

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

id	date	Value
1	6/4/2014	45
2	10/3/2014	0
3	10/16/2014	4
12	11/7/2014	2
2	11/13/2014	23
12	12/29/2014	4
1	1/12/2015	0
1	2/26/2015	2
12	3/19/2015	0
3	4/14/2015	0
2	4/20/2015	0
1	5/22/2015	3
23	7/17/2015	1

[palanimurugan]

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]

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'

id	Start Date	End Date	Values
0001IA-1	2014-02-18	4/2/2015	0-0
0001IA-1	2014-11-25	1/21/2016	553
0001IA-1	2015-06-03	6/23/2016	886
0001IA-2	2015-03-20	5/23/2016	946
0001IA-2	2015-10-13	11/10/2016	963
0001IA-2	2015-02-04	9/22/2016	0-0
0001IA-3	2015-09-25	11/10/2016	1002
0001IA-3	2014-10-21	11/9/2015	1062
0001IA-3	2015-09-29	9/29/2016	1083
0001IA-4	2015-06-18	6/27/2016	613
0001IA-4	2015-05-21	5/23/2016	643
0001IA-4	2014-02-25	6/1/2015	672
0001IA-4	2015-12-02	12/5/2016	0
0001IA-5	2015-11-18	12/22/2016	729
0001IA-5	2014-04-25	4/27/2015	730
0001IA-5	2015-01-28	6/20/2016	733
0001IA-6	2014-08-27	10/1/2015	785
0001IA-6	2014-05-07	6/25/2015	0
0001IA-6	2015-06-03	9/5/2016	214
0001IA-7	2014-09-24	10/1/2015	222
0001IA-7	2014-04-03	4/30/2015	0
0001IA-7	2014-07-04	7/23/2015	249
0001IA-7	2015-05-26	6/9/2016	254
0001IA-7	2016-01-19	2/16/2017	264
0001IA-8	2016-06-03	6/8/2017	266
0001IA-8	2014-06-13	9/3/2015	300
0001IA-8	2014-09-23	10/26/2015	470
0001IA-8	2014-04-28	4/30/2015	553
0001IA-9	2015-05-26	6/9/2016	0-0
0001IA-9	2015-06-03	6/9/2016	211
0001IA-9	2014-03-11	10/12/2015	0-0
0001IA-9	2014-07-17	9/28/2015	215
0001IA-10	2015-05-05	6/27/2016	216
0001IA-10	2015-04-02	6/6/2016	218
0001IA-10	2016-04-24	4/24/2017	0-0
0001IA-10	2015-10-22	12/28/2016	243
0001IA-11	2015-01-16	3/9/2016	253
0001IA-11	2015-03-10	6/6/2016	258
0001IA-11	2016-02-26	3/20/2017	261
0001IA-12	2014-08-11	11/30/2015	268
0001IA-12	2014-03-20	12/2/2015	298
0001IA-12	2014-10-03	12/9/2015	307
0001IA-13	2014-10-30	12/21/2015	455
0001IA-13	2016-02-24	3/13/2017	599

[sunny_talwar · Accepted solution]

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]

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 below

[sunny_talwar]

Please share your qvw.

[palanimurugan]

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?

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

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

[palanimurugan]

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