NonNumeric Characters

2 followers
0
Avatar

I have pulled in a dataset which has the employee login which should be 6 numerics.  However, the data includes some nonnumeric characters (maybe contractors?) and so the column is STRING and not INTEGER.  

If I convert the column using INT(<column>) it will fail.

I couldnt find a function to test for Numerics... or maybe I am looking in the wrong place.  But I need to filter out these non numerics rows in DataMeer so I can join it onto another sheet which has the join field as numeric.

Wedmore

2 comments

  • Avatar
    Alan Mark

    Hi Wedmore,

    If you just want to drop these records - the easiest way to do that is in your Data Link or Import Job on the Define Fields step.

    Change the column to Integer, then scroll down to the How to handle invalid data? section, expand it, and make sure it's set to Drop record.

    If you want to keep these columns, you'll need to use a nested IF CONTAINS_IC with a Regex such as [A-Za-z] to identify, then either drop alphanumerics or convert them into numbers.  I caution against converting to numbers unless you have another way to identify them as contractors - while unlikely - it would be possible to end up with a non-unique value.

    Note, IF and CONTAINS_IC are clickable above and will send you over to the documentation pages for these functions.

    If you need to qualify the regex to use, I commonly use the site regex101.

    0
  • Avatar
    Wedmore

    Thanks Alan.  I went with the import/drop record solution since I wouldn't be able to use/join on non-numeric regardless.

    0
Please sign in to leave a comment.