How to omit values from a field if it contains a letter inside it

Utz
Utz Member
edited May 10 in SQL DataFlows

I have a really quick simple question I am using the field "item Number" but I need to omit any Item Numbers that contain the characters "A, B, C, P" I tried using the filter tool on the analyzer and used the Match Values option set that to does not contain "A, B, C, P" but that doesn't seem to work

Here is the screenshot of it:

image.png

Another method I have tried is putting this formula in my Dataflow "ItemNumber BETWEEN '10000' AND '99999' " so it gives me only numbers without any leading letters but this does not seem to produce the correct values either

Is there a case statement I can write in domo beast mode analyzer that would give a true and false based on if there are item numbers that have "A, B, C, P" inside them? Beast mode doesn't seem to have a contain or include a function for me to use.

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    If those are the only letters that will appear in the values of that field you could create a beastmode:

    CASE WHEN `ItemNumber` LIKE '%A%' OR `ItemNumber` LIKE '%B%' OR `ItemNumber` LIKE '%C%' OR `ItemNumber` LIKE '%P%' THEN 'Out' ELSE 'In' END

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    CASE WHEN `ItemName` LIKE '%A%' OR `ItemName` LIKE '%B%' 
OR `ItemName` LIKE '%C%' OR `ItemName` LIKE '%P%' THEN 'Ignore' ELSE 'Keep' END

    You can use the LIKE statement to see if your string contains other characters. Alternatively you can use a REGEXP_REPLACE in Magic ETL formula tile:

    CASE WHEN REGEXP_LIKE(`ItemName`, '[ABCP]') THEN 'Keep' ELSE 'Ignore' END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    This solution worked on one of my datasets, might be worth a shot if there is a wide set of letters that could be in your field. Otherwise, @ColemenWilson and @GrantSmith's suggestions will do the job.

    I created this beast mode:

    case when length(`field`) = length(`field` * 1) then `keep' else 'ignore' end

    Multiplying the field by 1 seems to truncate to the numerical value prior to the letter in the string, so if the lengths do not match after the multiplication, then a non-numeric character must have been present.

    If someone knows this method is flawed, let me know!

Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    If those are the only letters that will appear in the values of that field you could create a beastmode:

    CASE WHEN `ItemNumber` LIKE '%A%' OR `ItemNumber` LIKE '%B%' OR `ItemNumber` LIKE '%C%' OR `ItemNumber` LIKE '%P%' THEN 'Out' ELSE 'In' END

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    CASE WHEN `ItemName` LIKE '%A%' OR `ItemName` LIKE '%B%' 
OR `ItemName` LIKE '%C%' OR `ItemName` LIKE '%P%' THEN 'Ignore' ELSE 'Keep' END

    You can use the LIKE statement to see if your string contains other characters. Alternatively you can use a REGEXP_REPLACE in Magic ETL formula tile:

    CASE WHEN REGEXP_LIKE(`ItemName`, '[ABCP]') THEN 'Keep' ELSE 'Ignore' END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Utz
    Utz Member

    Thank you so much for the quick response @ColemenWilson and @GrantSmith appreciate it!✊

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    This solution worked on one of my datasets, might be worth a shot if there is a wide set of letters that could be in your field. Otherwise, @ColemenWilson and @GrantSmith's suggestions will do the job.

    I created this beast mode:

    case when length(`field`) = length(`field` * 1) then `keep' else 'ignore' end

    Multiplying the field by 1 seems to truncate to the numerical value prior to the letter in the string, so if the lengths do not match after the multiplication, then a non-numeric character must have been present.

    If someone knows this method is flawed, let me know!

  • GrantSmith
    GrantSmith Coach

    @Sean_Tully - I've used that method as well to convert a string to an integer. I've found it works well.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories