How would I filter for Order Numbers that contain UN codes? While keeping all other part numbers

levi_kim

Hi all,

I am trying to figure out how to do this in Analyzer via Beastmode or filter tool. I am trying to only filter/display Orders that contain a product with a UN battery code while still displaying all other items in that Order Number. I'm having a tough time finding a function that could do this. Any ideas? Thanks in advance!

ColemenWilson

Create the following beastmode:

CASE WHEN `UN Number` IS NOT NULL THEN 'Include' ELSE 'Exclude' END

Then drag the newly created beastmode into the filter section in analyzer and filter to include "include"

DavidChurchman

I'm noticing your formula should be !='' or >=1 to get the non-empty results.

It's also possible in addition to an empty string, you could have a string comprised of white spaces, which would have a length greater than 0 and not be equal to ''

''

vs.

' '

To account for that, I think wrapping your code in TRIM() should remove any invisible extra spaces that might be in that column.

Adapting @ColemenWilson's answer, you could do something like:

CASE

WHEN `UN Number` IS NOT NULL THEN 'Include'

WHEN trim(`UN Number`) <> '' then 'Include'

ELSE 'Exclude'

END

ColemenWilson

Create the following beastmode:

CASE WHEN `UN Number` IS NOT NULL THEN 'Include' ELSE 'Exclude' END

Then drag the newly created beastmode into the filter section in analyzer and filter to include "include"

levi_kim

Hi @ColemenWilson ,

Thanks for your response. I tried that beastmode and applied it but it still displays Order Numbers that do not have UN Number. An example below:

Null fields can be tricky sometimes with our datasets. We've had to use prompts such as When UN Number = '' and when length('UN Number') < 1 as well because they can be empty strings. So I will try that.

Edit: I tried this and got the same result as before.

DavidChurchman

I'm noticing your formula should be !='' or >=1 to get the non-empty results.

It's also possible in addition to an empty string, you could have a string comprised of white spaces, which would have a length greater than 0 and not be equal to ''

''

vs.

' '

To account for that, I think wrapping your code in TRIM() should remove any invisible extra spaces that might be in that column.

Adapting @ColemenWilson's answer, you could do something like:

CASE

WHEN `UN Number` IS NOT NULL THEN 'Include'

WHEN trim(`UN Number`) <> '' then 'Include'

ELSE 'Exclude'

END

ColemenWilson

Ah good ol' empty strings!