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

[sunny_talwar]

This is something you are hoping to fix in the in the script?

[palanimurugan]

Yes, Sunny... I need to fix this while loading the data.

[Chanty4u]

Hi

1 : in script windows, go on table file load,

2 : load your file, but don't apply now -> next,-> enable transformation step

3 : go in fill tab, and choose, -> fill -> target column 2 -> cell condition (is empty) // above

NEXT NEXT FINISH

It will fill empty rows with above value

[Chanty4u]

Not tested may be try this

if(IsNull([Value]),Peek([Value]),[Value]) as New;

[sunny_talwar]

You mean If(Value = 0,....)?

[Chanty4u]

yes sunny

[palanimurugan]

Dear Sunny,

I have tried my end but it is not working for me.

Below is the script.

test:

LOAD id,

     date,

     Value

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Replace(2, top, StrCnd(equal, '0'))

));

Sample Data

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
23	9/25/2015	0
2	10/2/2015	12
1	11/11/2015	4

[sunny_talwar]

May be this

Table:

LOAD * INLINE [

    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

    23, 9/25/2015, 0

    2, 10/2/2015, 12

    1, 11/11/2015, 4

];

FinalTable:

LOAD id,

date,

Value,

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

Resident Table

Order By date;

DROP Table Table;

[palanimurugan]

Hi Sunny,

Great thanks!!.. this is working fine....