Transform to Decimal
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?
Hey! You will be signed out in 60 seconds due to inactivity. Click here to continue using the site.
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 https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/create-functions/#default-functions
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.
Add further conditions to the 'If then else' including an error message to highlight any issues with future values
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?
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.
@DTAconsulting you can make your function a reusable function. So you can always re-use it after the initial set up.
I had another go at this using the Regex I mentioned to get a better solution without using an If then else.
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