This article shows how to use transformations to clean and combine fields and use chained transformations to form a unified output value from inconsistent formatted input values.
For this example we have a very simple source file that contains company details, including a inconsistently entered VAT number.
The goal is to form a unified output value for the VAT number that starts with the country code and is followed by the cleaned VAT number, like the example below. Besides the VAT Number column, we’ll use the Country Code column.
NL301234567B01
The approach is to first clean the source value by using the Clean Text transformation and selecting the cleaning option Clean Text. We’ll call the output field VAT-cleaned.
The next step is to determine which of the cleaned source values start with a numeric value. We’ll do this with two transformations. The first one gets only the first two characters from the cleaned source value. The transformation that is used is Find Pattern.
These two first characters from the cleaned source either are the country code or the first part of the real VAT number. We can use the Clean Text transformation to check if the value converts to a number. If the value does not convert to a value (like ‘NL’ or ‘GB’) then the output is 0 (zero).
Now let’s use the output of the newly created column to determine if the source value already has the country code in it. If the output is 0 (zero) then the country code is already in the source value.
Before we continue to use this output value, we’ll in parallel prepare the cleaned source values that don’t have the country code. We’ll add the country code to all source values but in the end we’ll only use the ones that don’t have the country code yet. The Merge Fields transformation is used here.
And now the last step. Look at the numeric values we get with the third transformation and use that value to decide which value to use: the merged values from the last transformation or the cleaned source value.
To do this, we use the Switch Fields transformation. If the input value is 0 (zero) then we’ll use the VAT-cleaned field, since the 0 indicates that the country code is already in the source value. In any other cases we faal back to the merged values. The resulted output column is called VAT-all-done.
The result is perfectly visible in the grid after hiding all the output columns that we don’t use for field mapping. The highlighted columns in the next screen print show the original source value and the result of our transformations.
Now you can map the VAT-all-done column to the corresponding field in SuperOffice.
An overview of the steps is provided below.