Skip to content

Transformer Trick

January 13, 2012

A quick time-saving trick I learned a whole back has to do with the transformer stage.  When you passing five or 500 columns, this will be helpful.

When you need to apply a function or formula to many or all the columns you are passing through a transformer, there is a tool called ‘Derivation Substitution’  To get here, select all of the columns you need to transform.  Right-click on a selected column derivation inside the transformer and a sub-menu will pop up.  Click on ‘Derivation Substitution…’

Here you can insert any function or formula you need to apply to your selected columns.  For instance, it is good practice in most occasions to trim and handle any nulls before you insert into your target.  Say all my columns are strings, so I would want to apply the Trim and NullToEmpty functions to all of my selected columns.  But if I have 100 columns, this could take a long time doing this one by one.  Instead, select all my desired columns, and go to the ‘Substitute Derivation’ menu.

Once there, I type in  Trim(NullToEmpty($1))  in the text box.  The $1 will replace with whatever the column you are passing in your transformer.

Take a look at the before and after images below.  The first image is before I applied the functions.  The second image is after I applied them.  You can see that the $1 replaces with the link_name.column_name.



Hopefully this little tick will help you save some time the next time you are developing.


From → Tricks and Tips

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: