ACL Character Function – Value() Function

Welcome again to the series ‘Audit Command Language Tutorial for Beginners’. We have come a long way in understanding many of the character functions in Audit Command Language. Those who have followed along this far, surely are able to take on data transformation tasks with comfort.

This post will add a completely new perspective for your transformation tasks. If you recall, in a video on youtube, we mentioned that when reading in any data files in ACL tool, try to always read every field as a character field. The reason we gave was that all the data must be captured. But there is a problem that arises from that approach. This problem is that numeric fields like amounts and dates cannot be directly manipulated because ACL doesn’t convert the values dynamically. For fields with numeric values, we can use the Value function to convert numeric values read in as characters values back to numeric values.

Syntax: VALUE(string, decimals)

Example in a script to create a new field using Value Function

value-function-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Value(ProdNo,2)

Example in a workspace to create a new field using Value Function

value-function-workspace

TEST_Field Computed
Value(ProdNo,2)

Both the above statements return the result where the numbers in the column ‘Product Number’ are returned as Numeric values in the new column ‘Test_Field’ shown in the screenshot below:

value-function-result

This is of course not the best example because you would never be performing any calculations on the ‘Product Number’. However, it demonstrated how to convert string field to numeric values.  Check out the function in action:

Try out all the techniques you’ve learnt on this site and share your thoughts with us.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.