Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

5 Replies
Anonymous
Not applicable
Author

Just thoughts

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

maxgro
MVP
MVP

see attachment

Anonymous
Not applicable
Author

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.