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: 
Not applicable

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

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!DateCompleted001.PNGDateCompleted002.PNG

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

8 Replies
sunny_talwar

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

Not applicable
Author

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={"*"}>)

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

Thanks Sunny...I found a solution.