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

levi_kim · February 16

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 · February 16

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 · February 16

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 · February 16

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 · February 16

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 · February 16

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 · February 16

Ah good ol' empty strings!

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

Best Answers

Answers

Categories