Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula to find the highest number in a string

mike_weaver
8 - Asteroid

I was wondering if I could get some guidance with this.

 

I have a string value in a text field from the interface. It looks like. In the middle of this string is something like, "1, 10, 25."

 

I can isolate that part of the string using regex. But I need to also find the highest value in that string. That value will be used in an action tool to update another tool. (Basically updating the maximum distance in that tool).

 

So I think I'm limited to one formula in the action tool to get there. Does anyone know how I can isolate just the 25 in this instance?

9 REPLIES 9
MichaelSu
Alteryx Alumni (Retired)

Hi @mike_weaver ,

 

Please see attached solution. You can use the Text to Columns tool to perform a Split to Rows, sort the data from highest to lowest, and use the Sample Tool to take the highest number from each string.

 

MichaelSu_0-1626298102135.png

 

 

Please let me know your thoughts.

 

Thanks,

Mike

 

 

Maskell_Rascal
13 - Pulsar

Hey @mike_weaver,

 

Here is a workflow that should work for you. This approach allows for any strings fields that contain both alpha and numeric values. 

 

Starting Data:

Maskell_Rascal_0-1626299940582.png

 

Workflow w/ Output:

Maskell_Rascal_1-1626299991126.png

 

Cheers!

Phil

mike_weaver
8 - Asteroid

These are good solutions. Unfortunately, I need to isolate that in an action tool. So I need a formula that can do it.

Jean-Balteryx
16 - Nebula
16 - Nebula

Why do you need to isolate that in an Action tool ?

mike_weaver
8 - Asteroid

The string comes from an API feed. The user enters an address and then one or more radii and that comes in on one text tool. The radii are applied to various reports and maps downstream. I need to isolate the largest number to find the maximum radius and that value needs to be fed to a spatial tool down the line. That tool doesn't accept field references for the maximum radius so I need to change it with an action tool.

Maskell_Rascal
13 - Pulsar

Hey @mike_weaver

 

So now that I better understand what you're trying to accomplish, I put together a down and dirty analytic app for you to experiment with. You can create a trade area from a specific field within the Trade Area spatial tool. 

 

Maskell_Rascal_0-1626363390717.png

 

So applying the original workflow of mine allows for you to parse out the largest radii and then apply that to the Trade Area tool. 

Maskell_Rascal_1-1626363451894.png

 

So in the below example the app is parsing out 20 as the max number, and then applying that to the rest of the workflow to create my trade area. 

Maskell_Rascal_2-1626363579461.png

 

If this solves the issue please mark answer as correct, if not let me know!

 

Cheers!
Phil

 

 

 

mike_weaver
8 - Asteroid

Thanks for that, but I should have been more specific. I need to update the Distance tool. Specifically, the field circled in red.

 

mike_weaver_0-1626366246973.png

 

(P.S. ignore the "no valid datasets found").

Maskell_Rascal
13 - Pulsar

You can set the Trade Area tool to Drivetime Minutes. Depending on what you're trying to accomplish, you can then use a Spatial Match or Spatial Process tool to get your desired output.

 

Maskell_Rascal_0-1626367671925.png

 

This is really your only option due to how your data is coming into the workflow. RegEx has no innate way to evaluate values, since it is a string parser. 

 

Edit: I should also point out that the Trade Area tool is the only spatial tool that allows you to use a field to assign a value like distance/minutes. 

 

mike_weaver
8 - Asteroid

Thanks so much for your help!

Labels