Extracting numeric values for calculation

I have attached the dataset. I am trying to extract the number of vehicle spaces and the number of bicycle spaces, to then calculate the difference between the total number of vehicle and bicycle spaces available.

I had a go using the formula tool (GetWord) function to add 2 new columns then subtracting the 2 columns, then using the Summarize tool sum up the total. Is there an easier way?

Also, is the GetWord function the easiest method to extract the numeric values?

Extracting numeric valus from a string for calculation.yxmd

Help

Accepted answers

FinnCharlton

Hi @CraigC_RG , I would use the RegEx tool's Parse function to do this: Screenshot 2023-06-26 101124.png

Hope this helps!

Extracting numeric valus from a string for calculation.yxmd

FinnCharlton

@CraigC_RG very good point, have amended the RegEx to avoid that: Screenshot 2023-06-26 141245.png

Extracting numeric valus from a string for calculation.yxmd

All comments

nagakavyasri

Could you attach the datasheet or zip the workflow?

CraigC_RG

Hi - I have tried attaching the datasheet.

4701229_H3S8C5JZ.xlsx

FinnCharlton

Hi @CraigC_RG , I would use the RegEx tool's Parse function to do this: Screenshot 2023-06-26 101124.png

Hope this helps!

Extracting numeric valus from a string for calculation.yxmd

CraigC_RG

Thank you. I haven't been using Alteryx that long and keep seeing the Regex expressions. I will use the support materials to try to get my head around it. If you don't mind me asking, have you got any suggestions/ tips to help me do that?

CraigC_RG

Hi @FinnCharlton I just noticed a slight issue in the Parsing tool of your workflow. In the "Detail" column, if there is only null value i.e. Record 21, then both new columns display a null value. As a result the totals are incorrect. Any suggestions as to how to replace the null values in the Detail column with "0"?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703