Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
mjmilian
New Contributor III

Loading Previous Data

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

1 Solution

Accepted Solutions

Re: Loading Previous Data

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;

Capture.PNG

View solution in original post

2 Replies

Re: Loading Previous Data

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;

Capture.PNG

View solution in original post

Highlighted
mjmilian
New Contributor III

Re: Loading Previous Data

Thank you Sunny.  That works perfectly.