Sometimes when you import data, you'll come across special values which have been used instead of a 'NULL'; this could be something like 'missing', '-999', or '01JAN1901'.
You might want to convert this to something a little more useful or specific to your model - e.g., useless text values to NULL, a specific numeric value to NULL or the MEAN(<variable>), etc.
This can be done in the Variable Transformation node using a 'CASE' statement, or you can do it using the 'Define Special Values' helper:
Step 1: Add a 'Variable Transformations' node and select your variable and 'Define Special Values.' Here I used 'workclass'.
Step 2: Select the value you want to change and whether you want to convert it to 'NULL'.
Step 3: If you don't want to convert to null, what are your other options? Well, this is what the 'create description field' is for. So let's check that box:
Step 4: This gives you the option to create a new field and insert values for each of the 'Special Values'. The 'Description field entry' next to the special value lets you define the value, and clicking the 'Modify' button above will let you set a different default for any non-special values. Below we set the value of that field to be the existing value, meaning that we'll only overwrite the '?'.
Step 5: Here, we substitute the value of 'Unknown' to appear instead of the '?', so we're going to end up with two new fields, the original one with 'NULL' and a different one with 'Unknown'.
Step 6: This is converted into CASE statements if you want to change them manually and for data lineage reasons.
And now you can see the original field and the results - one with NULL and one with 'Unknown':
------------------------------
Alex Gobolos
Sales Engineer
Datawatch Corporation
Toronto ON
------------------------------
