Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Hart
Contributor II
Contributor II

Generate missing hour data

Hi all,

I'm trying to display a bar graph with the latest 24 hour data for warehouse data. The data we receive only has hours were there has actually been movement in the warehouse. 

Michael_Hart_1-1635468308779.png

I wish to display the entire 24 hours of data on the chart and for any hours that do not have data to have 0.

Michael_Hart_0-1635468200353.png

Any help is appreciated, thank you.

3 Replies
anthonyj
Creator III
Creator III

Hi @Michael_Hart ,

This code will loop through all the values from 0 to 23 and create a column called [Hour]. This will associate with your current column filling in the blanks.

Hours:
Load
0 + iterno()-1 as Hour
AutoGenerate 1
while 0 + iterno()-1 <=23;

Thanks

Anthony

Michael_Hart
Contributor II
Contributor II
Author

Hi @anthonyj ,

Thank you for the reply, 

The code does create the 0-23 hour rows and I can see the association in the data model viewer. But as soon as I put in my measure the hours still disappear.  

Thanks,

anthonyj
Creator III
Creator III

Ah yes, the measure will suppress nulls in the dimension so there needs to be a record created where none exists.

If you want a quick fix you could try adding a statement in your measure like:

if(sum([To Warehouse] > 0, sum([To Warehouse], 0)

Or

In your load script you could add the missing hours to your table with a dummy value of "0" for Warehouse and Staging columns. Just be careful. If you're aggregating with counts then this will artificially add rows. If it's sums then it is okay.

After loading your data tables take the Hours data and concatenate it to the bottom like:

// Add this section below your table that holds Hours and your measures.

Concatenate

load

Hours,

0 as [Your warehouse column],

0 as [Your staging column]

Resident Hours

where not exists(Hours);

Drop table Hours;

 

Thanks

Anthony