Transform to Decimal

DTAconsulting

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?

Thanks

Josh Boxer

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

DTAconsulting

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?

DTAconsulting

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 Boxer

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"

DTAconsulting

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 Boxer

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 See

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

Josh Boxer

Reusable_2dp_Function.dmx

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