Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Highlighted
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

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

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

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

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

Not applicable

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

Hi...

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

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

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

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

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

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

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.

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

May be check this link out

NULL handling in QlikView

Not applicable

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

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

Not applicable

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

Thanks Sunny...I found a solution.