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

TripleZ
TripleZ Member
in Magic ETL

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

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • TripleZ
    TripleZ Member

    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
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    TripleZ Member

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

    Mike

Categories