Records with Null Values are going unaccounted for... What syntax can I use within the “Expression Editor” for them to be included?

[#1]

20170607

Qlik Sense Desktop 3.2 SR3

Hello All...this is my first Programming App question ever!...Be Nice...

Question: Records with Null Values are going unaccounted for.... What syntax can I use within the “Expression Editor” for them to be included?

Prerequisite:

I cannot change the data before or during Import. (No use of LOAD or SELECTED scripts possible.) The COMPLETION_DATE date field is loaded as a string (General text).

The rest is presented within the screen shots attached.

I tried "IsNull" clauses, "Replace" clauses, "If, Then, Else" clauses to no avail.

Thanks in advance!

[sunny_talwar]

Have you tried to check 'Include Zero Values' under Add-Ons -> Data Handling?

[#3]

Hi...

Thanks for the quick response. I'll give it a try! 

[rubenmarin]

Hi Latief, you can also obtain that number from the other two:

(Count closed)-(count close with date)

The IsNull version can be:

=Sum(If(IsNull(COMPLETION_DATE), 1))

Another version using set analysis (should give better performance):

=Count({$-<COMPLETION_DATE={"*"}>} CLOSED) // Has to be adjusted to your "count all closed expression"

The bold part means: From the current selection ($), exclude ( - ) all with a completion_date (<COMPLETION_DATE={"*"}>)

[#5]

Update:

I only had a few minutes to hone in on using the “Include Zero Values' under Add-Ons -> Data Handling” option.

I think it was unsuccessful because the value isn't zero but Null?

I'll be digging in some more shortly…I appreciate the help!

Latief S. Pinder

Programmer Analyst

Department of Behavioral Health and Intellectual disAbility Services

Phone 215-685-5318

Fax 215-685-5581

mailto:Latief.Pinder@Phila.Gov

This email may contain confidential and/or privileged information. If you are not the intended recipient,

please notify the sender and delete this email immediately. Any unauthorized copying, disclosure or

distribution of material in this email is strictly forbidden.

[#6]

Thanxs for the suggestions Rubin…

I'll begin tinkering with this matter in a few minutes and will let you know how I made out!

Latief S. Pinder

Programmer Analyst

Department of Behavioral Health and Intellectual disAbility Services

Phone 215-685-5318

Fax 215-685-5581

mailto:Latief.Pinder@Phila.Gov

This email may contain confidential and/or privileged information. If you are not the intended recipient,

please notify the sender and delete this email immediately. Any unauthorized copying, disclosure or

distribution of material in this email is strictly forbidden.

[sunny_talwar]

May be check this link out

NULL handling in QlikView

[#8 · Accepted solution]

Hi Rubin...

I almost forgot to reply...

Your response was instrumental in me finding a solution. Thank you much for your insight.

ANSWER:

Rows: =if(INTAKE_STATUS ='CLOSED',If(IsNull(left(COMPLETION_DATE,10)), '_CmpDte N/A',left(COMPLETION_DATE,10) ))

Measures:  =Count({$+<INTAKE_STATUS={"*"}>}'CLOSED')

1-There were other records with “NULL” COMPLETION_DATE ‘s with INTAKE_STATUS’s other than 'CLOSED' . I only needed to account for 'CLOSED'.

2-The COMPLETION_DATE was imported as a general text with a full timestamp…Hence my need to truncate the data down to just the “year, month and day”.

3-I wanted to show that a COMPLETION_DATE  was unavailable, aka “_CmpDte N/A”.

Thanks again!

[#9]

Thanks Sunny...I found a solution.