Data360 Analyze

I have several fields in my file which contain both numeric and null/empty values.  These fields are initially string and I would like to convert them to numeric, changing the null/empty values to zero.  I have tried replace and isnull function but can't seem to get anything to work.  Any advice?

This will work:

c=long("0"+MyField.trim())
override emit c as MyField

In recent versions of Dataverse, the use of the proprietary Dataverse Script has been superseded by the Python language.

While the Dataverse Script [and the original Transform (Superseded) node] is still available for existing data flows, Lavastorm recommends the use of Python in the Transform node for current projects.

With this in mind, here is a data flow that shows how multiple string type fields can be converted to int type fields or float (double) type fields.

To configure the conversions you need to add the names of the integer type field and double/float type fields to the appropriate list in the 'ConfigureFields' script.

The node uses the lists of fields to remove the existing metadata for the specified fields and then re-creates the metadata for the fields with the appropriate data type.

The script in the 'ProcessRecords' property performs the value conversions. In addition to converting NULL values to zero it also converts empty strings and strings containing only whitespace characters to zero.

Note: you will need a minimum of Dataverse 3.1.6 to import the .lna file.

Attached files

Convert_Null_values_to_Zero - 29 Nov 2017.lna