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.
Make sure that Date is selected in the Transpose Tool and Select Tools. Sometimes they drop off when downloading a workflow from another user.
Its' worth noting that the data you posted is fairly different than the data your workflow was designed for - that has an effect on the how the workflow is developed. Your workflow had text fields and needed the first 4 rows cut off to be used - the data you posted didn't need that work and was already numbers. This changes how the workflow is developed and the tools used - the key component though is getting a date/group field and getting the tax rates into a double format (whether natively or via multi-field) and then using a summarize.
Hi @mzubair9402 Can you share your workflow or at least some sample data? Can't really troubleshoot the problem without seeing the issue.
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.
Sorry about that. Below is my workflow.
Can you also provide the underlying Excel file?
Sure Np.
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...
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...
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
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.
Thank you all for your help.
