getting max %

[mzubair9402]

Hi all,

I have a bunch of data in one column with numbers words percent and etc. I need to extract the max tax rate percent for each row. I tried the sum function but not all data being pulled is correct. Can you please help.

[Maskell_Rascal]

Hi @mzubair9402 Can you share your workflow or at least some sample data? Can't really troubleshoot the problem without seeing the issue. 

[apathetichell]

Is everything in one field???? Usually you'd group on what you want to group on and take the max value (in the summarize tool) but from your description this could be a situation where you need to break up/parse your data into proper fields first.

[mzubair9402]

Sorry about that. Below is my workflow.

[Maskell_Rascal]

Can you also provide the underlying Excel file?

[mzubair9402]

Sure Np.

[apathetichell]

I can tell you without opening your data your problem is your conversion to a number in your select tool - you need to be using a multi-field formula...

[apathetichell]

Swap your select for a multi-field formula and highlight the text columns you want to make numeric (ie the years/tax rates).

use this formula in the body:

tonumber([_currentfield_])

make sure name change is unclicked and click on change field type and put the drowpdown on double. Should work after - your datafile's formatting is fairly different (ie it starts on row 1 - not row 4 and doesn't include the column names) so it's easier to just describe it...

[Maskell_Rascal]

Hey @mzubair9402 

Here is a solution that might work for you. 

I'm using some RegEx functions and the Transpose/Crosstabs tools to make this as dynamic as possible. I also made the assumption that any percentage within the text field was a tax rate that needed to be extracted. 

Attached is the workflow for you to try out. 

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

Cheers!

Phil

[mzubair9402]

Thanks for all the help. This is my very 1st workflow. Also I downloading your solution but it looks like the states are not showing up in the sum tool, which won't let me run it.