Fill in values for all previous and next records

[jordan231]

I have a list of many products joined with combination of all dates. Some Products change value over the time so i need to assign

a valid value in exact time..  Any ideas how i can handle that ?

Product	Month	Value	NewField
A	201708		15
A	201709		15
A	201710	15	15
A	201712		15
A	201801		15
A	201802		15
A	201803		15
B	201708		20
B	201709	20	20
B	201710		20
B	201712		20
B	201801	30	30
B	201802		30
B	201803		30
C	201708	60	60
C	201709		60
C	201710		60
C	201712		60
C	201801		60
C	201802	80	80
C	201803		80

[Anonymous]

Hi,

Could you please elaborate more to understand the requirement.

Cheers

Paridhi

[sunny_talwar]

I understand the forward filling of data... but backward filling?

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    Product, Month, Value

    A, 201708,

    A, 201709,

    A, 201710, 15

    A, 201712,

    A, 201801,

    A, 201802,

    A, 201803,

    B, 201708,

    B, 201709, 20

    B, 201710,

    B, 201712,

    B, 201801, 30

    B, 201802,

    B, 201803,

    C, 201708, 60

    C, 201709,

    C, 201710,

    C, 201712,

    C, 201801,

    C, 201802, 80

    C, 201803

];

Table1:

LOAD *,

If(Product = Previous(Product) and Len(Trim(Value)) = 0, Peek('NewFieldTemp'), Value) as NewFieldTemp

Resident Table

Order By Product, Month;

DROP Table Table;

FinalTable:

LOAD *,

If(Product = Previous(Product) and Len(Trim(NewFieldTemp)) = 0, Peek('NewField'), NewFieldTemp) as NewField

Resident Table1

Order By Product, Month desc;

DROP Table Table1;

[jordan231]

Sure, My table has 3 fields , Product, YearMonth and Value valid for Month.

In case abouve

Product A has one value in 201710 so i would like to populate this value to other cells. Until today Year Month

Product B has two values 201709 and 201801 i would like to assign value from 201709 to all months before 201709 and before 201801. Value from 201801 after this month until new value appear.

Product C has two values first one 201808 will be valid until 201801, 201802 has new value and should be populate until 201803.

The result i'm looking for presented below.

[jordan231]

This is what i was looking for !

[sunny_talwar]

Great

[sasiparupudi1]

May be like below

T1:

Load * Inline

[

Product,Month,Value,NewField

A,201708,,15

A,201709,,15

A,201710,15,15

A,201712,,15

A,201801,,15

A,201802,,15

A,201803,,15

B,201708,,20

B,201709,20,20

B,201710,,20

B,201712,,20

B,201801,30,30

B,201802,,30

B,201803,,30

C,201708,60,60

C,201709,,60

C,201710,,60

C,201712,,60

C,201801,,60

C,201802,80,80

C,201803,,80

];

T2:

NoConcatenate Load

If((Product=Peek(Product) AND Len(Trim(Value))) OR (Product<>Peek(Product) AND Len(Trim(Value))),Value,Peek(x)) As x,

Product,Month,Value,NewField

Resident T1

Order By Product, Month

;

Drop Table T1;

T3:

NoConcatenate Load

If(Len(Trim(Peek(x)))=0 and Len(Trim(x))=0,Peek(y),if(len(trim(x))=0,Peek(x),x)) as y,

Product,Month,Value,NewField,x

Resident T2

Order By Product, Month desc

;

Drop Table T2;

[berndjaegle]

Check out this post.

With Rob Wunderlichs approach you can work with high and low levels.

Helped me a lot to generate age categoeries.

creating an age group from a date field

BR