There are three golden rules it's worth remembering - all to do with how DTL interacts with the normalizer and a Bunker.

If you break these rules, your script will fail completely, and your Bunker will automatically disable your most recent set of changes. If you see a message telling you this has happened, go back into the Transformations dialog to edit the script and resolve the problem.

Assign all the columns you need

As we saw in the first part of this series, to set up a transformation script using Bunker, you need to:

• use Assign Category to map one or more columns onto a category
• then use Transformations configure a transformation for that category.

This means that each transformation script is attached to a specific category. It can only read and transform the columns which are assigned to that category. Or to put it another way, each column you need to read or transform must be assigned to the category you're working with.

Example

For example, suppose you use a Bunker to map a column called Full Name onto the category Custom Key #1. You then use that Bunker to create a transformation script for that mapping.

In this case:

• Your transformation script can refer to Full Name, but if you refer to (say) Age, you'll get an error.
• If you need to use the value of the Age column as part of your transformation script, then you must use Bunker's Edit Category option to add it to the category.

End with the right number of columns

Each category expects to have a specific number of columns assigned to it.

Many categories expect to have just one assigned column, containing the category's value. But the category called Name, for example, expects two columns - one for the first name and one for the surname. And the category Address can have a varying number of columns assigned, reflecting that addresses can be written and stored in multiple ways.

Some DTL transformations add or remove columns, so the number of columns can change as your transformation script runs. While the script is running, you can have as many columns as you need. But by the end of your script, the number of columns remaining must be correct for the category.

Example

Continuing the example above, you have used Bunker to assign the two columns Full Name and Age to the category Custom Key #1.

• Like most categories, Custom Key #1 requires just one column. But you have assigned two columns (Full Name and Age) to this category.
• So at the start of your transformation script, you have too many columns. By the end of your script, you need to have just one column remaining.
• How you achieve this is up to you. You could store your transformed value in the Full Name column and use the drop transformation to remove the extra Age column. Or you could store the transformed value in a completely new column, and drop both the originals.

End with the same number of columns in each row

Some categories, like Address, accept a varying number of columns. If you're working with one of these categories, then your transformation must end with the same number of columns in each row.

For example, every row could contain four columns, or every row could contain five columns, but you can't have some rows with four columns and some rows with five. You may need to think about this if your script uses if statements to create multiple logical paths.

Example

The following code attempts to remove the Town column if and only if it contains the empty string. Assuming this is the only thing the script does to the Town column, it will cause an error, because some rows will end up with a Town column and others will end up without it.

// Will cause an error

if `Town` == ""
{
  drop ("Town")
}