I need to force display of null values (I think?!)

[drminaker]

I've seen a number of posts related to this same issue, but I've not been able to get anything to work for my specific case. I'm hoping someone can help, as I believe this is pretty straight forward or I'm just missing the logic or functions!

I'm loading data into Qlik Sense that I hope will enable me to track who has not attended training, based on a course that I could select from a filter.

I have two tables:

• A table with the names of all the staff in the company [MasterList]
• A table with the names of all the staff in the company who have attended training [AttendedList]

So I have my master list of all staff, and I have the list of everyone who has attended training. When I load the data into Qlik Sense, I can easily see who has taken training, but I can't figure out how to see who has not?

For example I have a table with [Name] and [Course Title] dimensions. I can see everyone who has taken the course, but I want the table to show everyone who has no also (i.e., I'm seeing 2000 records when I should be seeing 3000, and 100o of those null).

I had initially thought it would be as easy as to look for the null values, but when I apply a filter based on course I don't see any, even though I know there are!

I spend a lot of time at the load script level trying to filter out names on load, but now I'm wondering if I should bring in all the data and try to filter it out inside Qlik Sense -- a way to force a null value to display maybe?

This is what my data looks like at the most basic level. Any help, much appreciated!

=============================

[MasterList]:   // Names of all staff

LOAD

[First Name] & ' ' & [Last Name] as [Name],

"Location"

FROM

[AttendedList]:   //Names of staff who have attended training

LOAD

[First Name] & ' ' & [Last Name] as [Name] ,

"Course Title"

FROM

[sinanozdemir]

Hey Ryan,

You can add the below two lines in your load script:

And all NULL values will be converted to <Unknown>.

Hope this helps.

[drminaker]

Hi Sinan,

Thanks for this. I didn't know I could change the display of NULL values in the script there. This is good to know! However, it didn't solve my problem... as I thought it would.

Maybe to look at this in another way (simpler?):

I have a bar chart using [Name] as the dimension and Count([Course Title]) as the measure. With no filtering/drill-down applied I can see everyone's name and a count of how many courses they've attended. (I also have a straight table using [Name] and [Course Title] as dimensions. So no problem seeing who did what.

When I pick a specific course from a filter, I only see the people who have attended, but I'd also like to see the people who haven't. (Example: I filter on 'ABC course' and I see that 10 people took it, but I also want to see the 75 people who didn't).

It seems it's just a matter of getting Qlik Sense to show me all the records (not just the ones with a value), but I just can't figure this out!

Thanks for any help.

R.

[sinanozdemir]

Have you tried to check "Show null values" under columns to show NULLs?

If this doesn't work, then you may have blank values rather than NULL values.

[drminaker]

I think you're right -- I'm mixing those up...

I've attached a sample of what I'm trying to achieve (source and Qlik Sense app).

I'd like to be able to filter on 'Course A' and see that the following people did not attend (in addition to who did attend).

Kevin

Laura

Nora

Denise

Robert

Caitlyn

Thanks for your continued help! I appreciate it!

[drminaker]

So I'm getting closer to figuring this out. I added a formula to my count to force the chart to show the missing values.

Count ( [Course Title] ) + 0 * Sum( {1} [Course Title] )

This works great, except I've hit another roadblock. When I export this to Excel, the names don't export for the zero values. I tried altering the formula so that zero values show as 1 and 1 show as 2 (so I'd know that anyone who did not attend was a 1 etc.), but the chart still won't export the names even with a value of 1...

If anyone knows what I can do to fix this, it would be much appreciated!

[sinanozdemir]

You can try this so that zero values get .001. This may keep the zero values on the spreadsheet:

Num(Count ( [Course Title] ) + 0.001 * Sum( {1} [Course Title] ),  '0.00')

[drminaker]

I can see the numbers, but the names are still blank when I export to Excel....