topic Re: Populate missing months in QlikView

Populate missing months

Mon, 01 Feb 2016 09:55:50 GMT

Hi All,

I have a table with data like below, in the table for the unit 7511 I am not getting some of the months data because of I didn't have sales for that unit on the messing month. however I would like to show ,unit number, missing month, '0' in the Sold column how do I get this, can any one help me on this.

Actual:

Unit	Month	Actual
7511	Sep 2014	139232
7511	Nov 2014	17023
7511	Dec 2014	7177
7511	Jan 2015	6827
7511	Feb 2015	2738
7511	Mar 2015	13245
7511	Apr 2015	12523
7511	Jun 2015	20164
7511	Jul 2015	5890
7511	Aug 2015	5071
7511	Sep 2015	11891
7511	Oct 2015	2757
7511	Nov 2015	7036
7511	Dec 2015	4195
7511	Jan 2016	6372

Expected

Unit	Month	Actual
7511	Sep 2014	139232
7511	Oct 2014	0
7511	Nov 2014	17023
7511	Dec 2014	7177
7511	Jan 2015	6827
7511	Feb 2015	2738
7511	Mar 2015	13245
7511	Apr 2015	12523
7511	May 2015	0
7511	Jun 2015	20164
7511	Jul 2015	5890
7511	Aug 2015	5071
7511	Sep 2015	11891
7511	Oct 2015	2757
7511	Nov 2015	7036
7511	Dec 2015	4195
7511	Jan 2016	6372

Re: Populate missing months

swuehl — Mon, 01 Feb 2016 09:57:33 GMT

Generating Missing Data In QlikView

Re: Populate missing months

sunny_talwar — Mon, 01 Feb 2016 10:51:14 GMT

Do you have a Master Calendar in your application? If not, I would suggest creating a master calendar with continuous date from min date in your database to the max date in your database. Once you do that, you should be able to see all Months. For a more precise help, would you be able to share a sample?

Re: Populate missing months

Mon, 01 Feb 2016 12:22:50 GMT

Hi john,

Please find the attached app for what you asked for. You can refine it further according to your requirement.

Hope it helps ..

Regards,

Lokeshwari

Re: Populate missing months

Colin-Albert — Mon, 01 Feb 2016 12:26:47 GMT

By default QlikView supresses zero rows from charts.

Can you check that the chart Properties, Presentation tab has Supress Zero values and Suppress Missing boxes not ticked.

If this does not show the values, then you will need to add a master calendar linked to your transaction dates so all dates exist in your data model.

Re: Populate missing months

Wed, 03 Feb 2016 11:16:57 GMT

Hi Lokeshwari ,

thanks for the response, I tried the way you are using however in my model I have 100,000 units , each unit has minimum date and maximum date also there are some missing dates as well so I have followed your steps.

I have taken the dates from 2001 onwards because of this script my application becomes huge. almost 250 MB.

is there any other way.

Re: Populate missing months

Wed, 03 Feb 2016 17:48:51 GMT

Hi Colin Albert

your points are taken, also I am providing some sample data which will let you know the problem I am facing,

I have five fields Unit, Date ,MonthYear ,Months, Actual, Accumulated Value.

here some of the Units doesn't have dates for some of the moths, however I need the missing month dates also, for that dates I need previous accumulated values. I tried the above options however I am getting performance issues here taking more time to reload the application.

could you please guide me on this.

Re: Populate missing months

Wed, 03 Feb 2016 18:04:48 GMT

Hi Sunny Please find the Sample data , in the sample data I am providing

Unit, Date, MonthYear, Months, Actual , Accumulated Value as fields

I need Continuos Dates for the Unit also need accumulated value for every date , if Accumulated value is not there for any of the date it should pick the previous date value.

Re: Populate missing months

Thu, 04 Feb 2016 05:52:08 GMT

Hi Stephen,

I have read the document , and tried the approaches ,however I didn't get the right results, when I ran my machine , my system is not responding.

I am taking data from 2005 on wards, I have 45000+ units , when I try to generate dates for each unit from 2005 to till date I am getting this issue.

My requirement is

I have units and its service dates , I need to show the Accumulated value for each month weather the dates are the are not if date is there corresponding accumulated value I need to show, if not there pick previous date accumulated value for that date.

Could you please help me on this. I have attached sample data also.

Re: Populate missing months

PradeepReddy — Thu, 04 Feb 2016 12:25:46 GMT

are you looking for something like this. see the attachment...

Re: Populate missing months

Thu, 04 Feb 2016 12:37:51 GMT

Pradeep sorry for my mistake , I have attached wrong test data earlier , please find the correct file above.

Re: Populate missing months

PradeepReddy — Thu, 04 Feb 2016 15:24:51 GMT

see the attachment...

I have restricted the data for 3 units only(7511, 7512, 7513), bcz of size issue. you can remove the condition in script..

Re: Populate missing months

Thu, 04 Feb 2016 15:36:50 GMT

thank you so much Pradeep taniparthy , could you please explain me the concept behind entire logic that you have written.

Re: Populate missing months

PradeepReddy — Thu, 04 Feb 2016 15:41:19 GMT

is this the expected result?

Re: Populate missing months

Thu, 04 Feb 2016 18:25:16 GMT

Yes you saved my time. , that is exactly what I want.

please explain me the script that you have written,i a, curious about the last iterno() section what is happening there?

Re: Populate missing months

PradeepReddy — Fri, 05 Feb 2016 05:03:22 GMT

Glad to help..

I am Calculating the no.days between the two sale dates of same Unit. (Jan-01-2015, Jan-03-2015 =2)

After that I am generating the missing dates between those 2 dates, by using loop.The loop will be iterated till 'no.of Days' calculated for each sale date entry

For 1st Iteration (Jan-03-2015)-1+1 --> Jan-3-2015

For 2nd Iteration (Jan-03-2015)-2+1 --> Jan-2-2015

For Accumulated values, I have used the Peek() function..

Re: Populate missing months

Fri, 05 Feb 2016 06:19:17 GMT

Thank you so much.

Re: Populate missing months

Fri, 05 Feb 2016 11:28:24 GMT

Pradeep I have followed the script procedure you have provided , however I am getting

General Script Error

Execution Failed after Iteration, to pick previous Accumulated value .

Re: Populate missing months

sunny_talwar — Fri, 05 Feb 2016 11:29:46 GMT

Can you share your script?

Re: Populate missing months

PradeepReddy — Fri, 05 Feb 2016 11:30:47 GMT

can u share your QVW?