MAX function excluding alphanumeric

Question

Hi! My data has 100 columns with numbers and alphanumeric entries. I would like the MAX value only for the numbers (excluding alphanumeric).  Thank you.

Example.

Column1Column2Column3...Column100MAXMAX (not what I want)80058030G048 99289928G048992893309300 G0379928G03781009928J703 80059928J703

bpatel · Accepted Answer

hi @jpbonilla ,

you can try something like this!

hope it helps!

jasperlch · Accepted Answer

Hi @jpbonilla

You could achieve this by:

1. Add a RecordID which is unique for each row

2. Transpose all the columns to one single column

3. Use a Filter tool to keep only values which are all numbers (using the Regex_Match function)

4. Convert the Value field to Int64

5. Use Summarize tool to find the Max numeric value for each row (i.e. RecordID)

6. Join the max data back to the original dataset using the RecordID field

7. Add a Union tool to complete a Left Join in case there are some rows with no numeric values at all