Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to retrieve the last known information in a table ?

Hi everybody ,

I need your help :

I have a table with those columns : Year/Month/ProductID/Amount

My problem is , for the actual month the column amount is always 0 , I'll like to create a new column who return the last amount known for a product.

For example :

YearMonthProductIDAmount
201610110
201611112
20161210
201410225
20161330
20161230
20161220

result expected :

YearMonthProductIDAmountLastAmount
20161011010
20161111212
2016121012
20141022525
2016133030
2016123030
2016122025

someone have an idea ?

1 Reply
sunny_talwar

Try this:

Table:

LOAD * INLINE [

    Year, Month, ProductID, Amount

    2016, 10, 1, 10

    2016, 11, 1, 12

    2016, 12, 1, 0

    2014, 10, 2, 25

    2016, 1, 3, 30

    2016, 12, 3, 0

    2016, 12, 2, 0

];

FinalTable:

LOAD *,

  If(ProductID = Previous(ProductID) and Amount = 0, Peek('Amount'), Amount) as LastAmount

Resident Table

Order By ProductID, Year, Month;

DROP Table Table;

Capture.PNG