Loading Previous Data

[MIKIEMILLER]

Hello,

What I am trying to accomplish is to sum all amounts for each year based on field 1 and field 2, but if a year has no amount, to use the previous one that is available.

Field1,Field2,Field3,Amount

1,B,2015,50

1,L,2015,75

1,B,2016,0

1,L,2016,0

1,B,2017,100

1,L,2017,125

2,B,2015,90

2,L,2015,120

2,B,2016,0

2,L,2016,0

2,B,2017,0

2,L,2017,0

3,B,2015,90

3,L,2015,120

3,B,2016,40

3,L,2016,80

3,B,2017,0

3,L,2017,0

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Field1, Field2, Field3, Amount

    1, B, 2015, 50

    1, L, 2015, 75

    1, B, 2016, 0

    1, L, 2016, 0

    1, B, 2017, 100

    1, L, 2017, 125

    2, B, 2015, 90

    2, L, 2015, 120

    2, B, 2016, 0

    2, L, 2016, 0

    2, B, 2017, 0

    2, L, 2017, 0

    3, B, 2015, 90

    3, L, 2015, 120

    3, B, 2016, 40

    3, L, 2016, 80

    3, B, 2017, 0

    3, L, 2017, 0

];

FinalTable:

LOAD *,

If(Field1 = Previous(Field1) and Field2 = Previous(Field2) and Amount = 0, Peek('New_Amount'), Amount) as New_Amount

Resident Table

Order By Field1, Field2, Field3;

DROP Table Table;

[MIKIEMILLER]

Thank you Sunny.  That works perfectly.