I have a column of part numbers and I need to exclude any rows where a part number has a character

TripleZ

Example Parts: '0055656','1248000', 'D9989008','0556767P'

I need to exclude rows where the third and 4th part in list above exist. First and last characters can be any member of the alphabet.

Thanks,

Mike

MichelleH

@TripleZ I agree with Mark's solution. You can also try using the below formula in a filter tile to check whether the value has only numeric characters. The REGEXP_LIKE function will return 1 if it matches and 0 if it does not, so you can specify that you only want to keep the matches

 REGEXP_LIKE(`Part`,'[0-9]*') = 1

MarkSnodgrass

You can use the split columns tile in Magic ETL to split up your string by looking for the comma. You would do multiple splits in that tile so that you can get to the 3rd and 4th entries. You can then do a filter tile where you filter out entries that have the values you want to exclude. Here's a KB article on the split columns tile.
https://domo-support.domo.com/s/article/360045402873?language=en_US

You can also use the SPLIT_PART function to do this in a formula tile.

TripleZ

I think I was unclear in my explanation. Each of those 4 values are in the same column on different rows. I need a way to look at a column and determine if the first or last character is non-numeric.

Thanks,

Mike

MarkSnodgrass

You can use LEFT(fieldname,1) to get the first character and RIGHT(fieldname,1) to get the last character. You can then test for numeric by using TRY_CAST(leftcharacter as integer). If it returns null then it is non-numeric.

I would create separate fields in your ETL for each of these functions so it is easier to test as you work through it.

MichelleH

@TripleZ I agree with Mark's solution. You can also try using the below formula in a filter tile to check whether the value has only numeric characters. The REGEXP_LIKE function will return 1 if it matches and 0 if it does not, so you can specify that you only want to keep the matches

 REGEXP_LIKE(`Part`,'[0-9]*') = 1

TripleZ

Thanks for the follow up. the REGEXP_LIKE worked for me.

Mike