topic Re: Expressions: if null, set empty-string; else if field <> x AND field <> y, then show in QlikView

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

llefever — Sat, 16 Nov 2024 03:11:06 GMT

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

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

marcus_sommer — Thu, 27 Jun 2019 14:28:21 GMT

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

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

Brett_Bleess — Tue, 23 Jul 2019 12:40:22 GMT

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