MAX function excluding alphanumeric

[jpbonilla]

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.

Column1	Column2	Column3	...	Column100	MAX	MAX (not what I want)
8005	8030	G048		9928	9928	G048
9928	9330	9300		G037	9928	G037
8100	9928	J703		8005	9928	J703

[bpatel · Accepted solution]

hi @jpbonilla ,

you can try something like this!

hope it helps!

[jasperlch · Accepted solution]

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

[jpbonilla]

Thank you!

[jpbonilla]

Perfect! Thank you guys!