Transform to Decimal

Aperture 2.3.075

I'm struggling to find a basic and simple function to preserve decimal places when cleansing a field that contains numerical information.

e.g. field contains "29.3 cm"

I'd like to remove the "cm" and keep decimal with precision of 4 and scale of 2, i.e. "29.30"

User documentation suggests there is a Transform Number function or Convert to Decimal but I cannot locate these.

Would 2.3.14 rectify this?



  • Josh BoxerJosh Boxer Administrator

    Hello, the Functions 'Replace' and 'Round number' should be available without needing to upgrade:

    If the replacement string, i.e. "cm" has a few variation then you could use an additional Replace or use the more advanced Regex replace:

    If you specifically wanted "23.90" rather than "23.9" then the 'Scale' Function will count the digits after the decimal point and you can 'Concatenate' those that need a zero added (note I made the function above into a Variable):

    Hope that helps. Where did you read in the documentation there is a Transform Number function or Convert to Decimal? We will review. Thanks

  • Thank you Josh,

    I'll try this out now.

    I read up here

    Transform Number also appears to be listed as Function but I can't access it within a Transform Step.

    Would I be able to do something different if I upgraded?

  • I've been doing some more tests and come across a situation where the above solution doesn't handle values that are whole numbers without a decimal place or where I have values to one decimal place

    e.g., 5cm, 10 cm, 15.0, 15

    result round orig value

    12.53 12.53 12.53 cm

    12.11 12.11 12.11

    29.40 29.4 29.4 cm

    50 5 5cm

    100 10 10 cm

    15.50 15.5 15.50

    15.30 15.3 15.3

    150 15 15.0

    150 15 15

    I'll try and work on a workaround solution.

  • Josh BoxerJosh Boxer Administrator

    Add further conditions to the 'If then else' including an error message to highlight any issues with future values

    • If scale is 1 then concatenate variable+"0"
    • If scale is 0 then concatenate variable+".00"
    • If scale is 2 then variable
    • Else Error message "Invalid"

  • DTAconsultingDTAconsulting Learner
    edited July 2021

    Thanks again Josh, however it doesn't appear to work when the value is a multiple of 10, 100, 1000 etc

    This results in a scale of -1, -2, -3 etc?

    Therefore I'm using Equals (strict) to get around this.

    Also I'd really like to have all results returned as a decimal number rather than left justified text.

    Is there a better solution in release 2.4?

  • Josh BoxerJosh Boxer Administrator

    Great that you found a solution (there are a few different approaches to those scenarios).

    Unfortunately there is not yet a simpler solution for this, but thanks for raising as this thread helps to prioritise.

  • Sueann SeeSueann See Experian Super Contributor

    @DTAconsulting you can make your function a reusable function. So you can always re-use it after the initial set up.

  • Josh BoxerJosh Boxer Administrator
    edited July 2021

    I had another go at this using the Regex I mentioned to get a better solution without using an If then else.

    1. replace any characters in the string that are not a number or full stop
    2. round to 2 decimal places
    3. parse so that each number in the string is placed in a comma separated list:

    Now you have a list, you can Pad zeros to ensure there are always 2 characters after the decimal point:

    Concatenate the first number then a full stop then the padded second number

Sign In or Register to comment.