Three rules to remember
To learn how to use DTL for your own transformations, you can work through our language tutorials. Before you do that, though, there are three golden rules it's worth remembering - all to do with how DTL interacts with the normaliser and your 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 Quick Start guide, 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.
For example, suppose you use Bunker to map a column called
Full Name onto the category
Custom Key #1. You then use 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
Agecolumn 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.
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 #1requires just one column. But you have assigned two columns (
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 Namecolumn and use the
droptransformation to remove the extra
Agecolumn. 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.
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` == ""