BellaDati Technical Documentation : Data cleansing

Icon

Goal of this use cases is to clean the values in imported data file.

Merging values

Icon

You can download data for this use case here.

Use contains function to transform values into the same value for all the rows. First you should define all the wrong appearances and then create condition for all of them. This function will update all the columns which contain selected string.

if(contains(value(2), 'wasser')) {
 return "Water"
}
if(contains(value(2), 'Warter')) {
 return "Water"
}
if(contains(value(2), 'water')) {
 return "Water"
}
return value(2)
Icon

This function will be used for all the values which contain selected string. If you want to update only specific values, you can also use conditions, like following:

if (value(1) == 'UK') {
return "United Kingdom"
}
return value(1)

Removing unwanted values

Sometimes a column should contain only specified values but thanks to some error in source system it also contains some unwanted values. In this example there is a column Gender which you should contain only values "Male" and "Female". Everything else should be replaced by "Unkown". You can use this script to get rid of them.

if (value() == "Male" | value()== "Female") {
return value()
}
else return "Unknown"

Similar way it is also possible to apply transformation scripts on indicators. For example you can get rid of values outside of range. In this example, we want to display all values greater than 1 as 0:

if (value()>"1") {
  return 0}
else return value()

Attachments:

sales.csv (text/csv)