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]

Hi,

Check the attached application.

Hope this helps you.

-Sridhar

[#3]

Thank you Sridhar I really appreciate your help.  I'm so sorry I made a mistake when copying my data over.  I corrected the mistake in the original post.   It seems that for MonthYear where the price is present the Price_Final is using the next price.  In this case if the price is present for a specific MonthYear it should use that price for the Price_Final.

Any idea how to fix this?

[sridhar240784]

Ok, i have check your o/p and designed this script. In your output table

for Product ABC --> Jun 2012 is using Jul 2012 Number and Jul 2012 is using Aug2012 Number and Nov2012 is using Jan 2013 num.

could pls confirm you o/p so that will change the script and post you soon.

-Sridhar

[#5]

Once again I appologize.  I corrected the o/p to display the proper end result.

[sridhar240784]

I have modified the script now.

Have a look at  the attched application now.

-Sridhar

[#7]

Thank you so much. Just one more question.  If I wanted to change to 36 months and use the last available price for those months how would I need to change the script? 

[sridhar240784]

if you want to add it at the front i.e. before Jan-2012 like Dec-2011, Nov2011. You may need to change the variable  which stores the minimum date for creating the master calander.

Let vMin = Num(Addmonths(YearStart(Peek('MinDate')),-23);

Hope this helps

-Sridhar

[#9]

No actually I would like it to always start with today and have the ability to change the months after to add more months like Jan2013, Feb2013... These extra records would need to use the last available price.

Hope this makes sense.  Thanks again.

[#10]

The idea is to be able to change the series for each product to display a term of any amount of months required.  It should not depend on the Min/Max MonthYear in the RawData.