topic Re: Fill in missing records on load in QlikView

Fill in missing records on load

Fri, 30 Dec 2011 14:11:33 GMT

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

Re: Fill in missing records on load

sridhar240784 — Fri, 30 Dec 2011 15:43:48 GMT

Hi,

Check the attached application.

Hope this helps you.

-Sridhar

Re: Fill in missing records on load

Fri, 30 Dec 2011 16:10:31 GMT

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?

Re: Fill in missing records on load

sridhar240784 — Fri, 30 Dec 2011 16:19:39 GMT

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

Re: Fill in missing records on load

Fri, 30 Dec 2011 16:21:49 GMT

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

Re: Fill in missing records on load

sridhar240784 — Fri, 30 Dec 2011 16:22:43 GMT

I have modified the script now.

Have a look at the attched application now.

-Sridhar

Re: Fill in missing records on load

Fri, 30 Dec 2011 16:27:19 GMT

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?

Re: Fill in missing records on load

sridhar240784 — Fri, 30 Dec 2011 16:35:05 GMT

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

Re: Fill in missing records on load

Fri, 30 Dec 2011 16:41:31 GMT

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.

Re: Fill in missing records on load

Fri, 30 Dec 2011 16:47:07 GMT

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.

Re: Fill in missing records on load

sridhar240784 — Fri, 30 Dec 2011 16:54:39 GMT

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

Re: Fill in missing records on load

Fri, 30 Dec 2011 16:58:46 GMT

This works great. Thank you for all your help.

Re: Fill in missing records on load

Fri, 30 Dec 2011 17:29:09 GMT

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

Re: Fill in missing records on load

sridhar240784 — Fri, 30 Dec 2011 17:38:16 GMT

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