Fill in missing records on load

[#1]

Hello All,

I have a data table that I would like to upload into my qlikview app but I would like to fill in the missing MonthYear data with the next available Price and if their is no Next record then use the Last available record.  I have put an example of the RawData and an example of what the data should look like after it is loaded.  I want to start the data as of Today and create 12 MonthYear data points for each product.  My initial thought is to loop through the record set but I am having trouble figuring it out.

I really appreciate any help on this.  Thanks in advance.

RawData

product	MonthYear	Price
abc	Mar-12	6.975
abc	Jun-12	6.5572
abc	Jul-12	6.456
abc	Aug-12	6.5056
abc	Nov-12	6.556
abc	Jan-13	7
xyz	Mar-12	20
xyz	Jun-12	21
xyz	Jul-12	22
xyz	Aug-12	23
xyz	Nov-12	24
xyz	Jan-13	25

UploadedData

product	MonthYear	Price
abc	Jan-12	6.975
abc	Feb-12	6.975
abc	Mar-12	6.975
abc	Apr-12	6.5572
abc	May-12	6.5572
abc	Jun-12	6.5572
abc	Jul-12	6.456
abc	Aug-12	6.5056
abc	Sep-12	6.556
abc	Oct-12	6.556
abc	Nov-12	6.556
abc	Dec-12	7
xyz	Jan-12	20
xyz	Feb-12	20
xyz	Mar-12	20
xyz	Apr-12	21
xyz	May-12	21
xyz	Jun-12	21
xyz	Jul-12	22
xyz	Aug-12	23
xyz	Sep-12	24
xyz	Oct-12	24
xyz	Nov-12	24
xyz	Dec-12	25

[sridhar240784]

Ok, I have changed the scrip now.

We have the data till Jan2013 in your sample data. i have modified my vMax varibale to take data till this year end. so now the month from Feb2013 will take the data from Jan 2013 and fill the below rows automatically.

Let vMax = Num(YearEnd(Peek('MaxDate')));

Check out the new field created for this 'Price_Final_YearEnd'

Hope this helps you.

-Sridhar

[#3]

This works great.  Thank you for all your help. 

[#4]

Sridhar, I just noticed that the new Price_Final_YearEnd is not using the correct prices.  Price_Final is using correct prices but it does not go to the maxdate .  Please let me know

Thanks

[sridhar240784 · Accepted solution]

ha.., Just noticed, small change in Price_Final_YearEnd field calculation.

Your expression should be as follows.

if(product = previous(product) and Len(Price_Final) = 0,peek('Price_Final_YearEnd'),Price_Final) as Price_Final_YearEnd

Check the attached application.

Hope this helps you again

-Sridhar