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

levi_kim
levi_kim Member
in Beast Mode

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!

image.png
Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    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"

    If I solved your problem, please select "yes" above

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    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"

    If I solved your problem, please select "yes" above

  • levi_kim
    levi_kim Member
    edited February 2024

    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:

    image.png

    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
    DavidChurchman Coach
    Answer ✓

    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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ColemenWilson
    ColemenWilson Coach

    Ah good ol' empty strings!

    If I solved your problem, please select "yes" above

Categories