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

Utz

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:

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.

ColemenWilson

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

GrantSmith

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

Sean_Tully

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!

ColemenWilson

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

GrantSmith

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

Utz

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

Sean_Tully

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

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