topic Re: Populating missing data for dates where product has no volume in QlikView

Populating missing data for dates where product has no volume

Anonymous — Tue, 15 Jul 2014 17:59:01 GMT

I'm trying to populate missing data in order to accurately calculate average and standard deviation for products over a given timeframe.

I have a master calendar populating Opened_At_Date for the last 60 days and then I have an SQL query that is pulling in the count of incidents created by product over the last 60 days by date (same format as Opened_At_Date). My problem is not every product has volume on every day. I need to find the dates where a product doesn't have volume and populate it with a zero. Thoughts?

Re: Populating missing data for dates where product has no volume

Anonymous — Tue, 15 Jul 2014 18:54:08 GMT

Just thoughts

What if you create a flag where volume is zero and then use that flag while pulling count of incidents.

Re: Populating missing data for dates where product has no volume

Anonymous — Tue, 15 Jul 2014 19:00:41 GMT

Again I am just thinking of different options

What kind of join you are using with master calendar and product data from SQL?

I think if you have Product table from SQL left join with master calendar table then where there is no volume that will have NULL in that column.

If you get this you can replace NULL with 0 using resident load.

Re: Populating missing data for dates where product has no volume

Anonymous — Tue, 15 Jul 2014 19:04:43 GMT

Again I am just thinking of different options

What kind of join you are using with master calendar and product data from SQL?

I think if you have Product table from SQL left join with master calendar table then where there is no volume that will have NULL in that column.

If you get this you can replace NULL with 0 using resident load.

Re: Populating missing data for dates where product has no volume

maxgro — Tue, 15 Jul 2014 20:21:01 GMT

see attachment

Re: Populating missing data for dates where product has no volume

Anonymous — Tue, 15 Jul 2014 23:35:12 GMT

I'll poke around this in the morning to see if I can understand what you have done. I see you commented out the statistics section so I will need to figure out how to make that work again.

Quick glance I think I could store Product_Volumes into a QVD then calc StDev and Avg from that QVD.