Complex Field Manipulations

While working with data files from overseas reporting centres you may come across a data file with a numerical field having a sample row value as 15.000,25

 

A closer look at the value will reveal that the 1000's separator is a dot unlike the customary comma and the decimal is a comma unlike the customary dot. The value gets captured in IDEA as a native character field.

 

Since it is a character field the user is unable to perform any numerical test on the same or even use the field in a numerical test.

 

In such cases the user can append a virtual numeric field and apply the following function to convert the character to a number -

 

@val(@replace(@replace(FIELD, ".", ""), ",", "."))

 

In the function above

 

@replace(FIELD, ".", "") will replace the 1000's separator dot with nothing

 

@replace(@replace(FIELD, ".", ""), ",", ".") will replace the decimal separator comma with a dot

 

@val(@replace(@replace(FIELD, ".", ""), ",", ".")) will convert the number in a native character format to a virtual numeric field.

 

Kind Regards

 

Jairam