Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
nicklisterman
Contributor II

Populating missing data for dates where product has no volume

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

5 Replies
swapsjosh
Contributor

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

Just thoughts

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

swapsjosh
Contributor

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

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.

swapsjosh
Contributor

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

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.

MVP
MVP

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

nicklisterman
Contributor II

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

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.