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: 
llefever
Contributor II
Contributor II

Expressions: if null, set empty-string; else if field <> x AND field <> y, then show field-value ...

Here's the Expression I'm attempting to use:

=if(IsNull(SPMKJLCE), '', if(RTrim(SPMKJLCE) = 'ER' OR RTrim(SPMKJLCE) = 'DC', Null(), SPMKJLCE))

... together with having "Suppress When Value Is Null" checkbox checked.

The idea is to AVOID filtering out any rows with SPMKJLCE being null, while filtering out rows with SPMKJLCE being equal either to 'ER' or to 'DC'; and otherwise show the relevant row with the given value in that column.

The problem  seems to be that rows containing, e.g., 'DC' (possibly also ones containing 'ER') in that column are remaining in the results though with empty-string as its value.

Anyway, can somebody confirm (or refute) that the above Expression is right for this use-case: i.e., "if null, set to empty-string; else, if 'ER' or 'DC', set to null and filter out the row; else show the column-value and retain that row"?

Labels (1)
2 Replies
marcus_sommer

Do you really want to set an empty string if the field is NULL? If yes, then is a check to len(trim()) often a better approach as using isnull(). If this isn't intended then I would probably use:

if(not match(RTrim(SPMKJLCE), 'ER', 'DC'), SPMKJLCE)

- Marcus

Brett_Bleess
Former Employee
Former Employee

Not sure if the following may be of any use or not, but there is also a white paper link at the end of the post too, hopefully it might give you some further ideas if Marcus' did not do the trick.  If you go back to the Qlik-Design-Blog level in the link, you can search the entire Design Blog, lots of great posts out there...

https://community.qlik.com/t5/Qlik-Design-Blog/NULL-The-Invisible-Nothing/ba-p/1467674

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.