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

[llefever]

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"?

[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]

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