Using Lookups to Transform Values

27 November 2012

In this simple example we will demonstrate how you can use the Lookup functionality in Data Synchronisation Studio to Transform/Convert a value via an Lookup Data source. Our source data contains the numbers 1-10 and we want to convert these into the names of the Numbers.

Data

We need then to create a Transformation Data source and for this we're going to use Excel. We could use virtually anything we like, SQL, CRM, SharePoint, XML, CSV etc but Excel is ideal for this. This Excel file contains the From and To values for the Transformation, we need to create Column headers in the First row and then the Data below i.e.

Data Source

Now we add the Lookup to the column (select the ID column, and press the 'Add Lookup' toolbar button) that we want to Transform, in our Example this is the ID column. First we select the Data source which for this example is Excel and browse to the Path of the Excel file.

Connect LookUp

Now we define the Lookup connection details, so we want to join the Lookup based on the Value in the From Column to the Value in the ID column in our source data. We also specify the Data Type as Int32 since this is a Number 1-10.

LookUp

We then add the “Lookup1_To” column from the Data source to our Schema Map so that we return the Value of the Lookup Transformation.

Columns

There we have it a Transformation from an External Data source via Lookups.

Preview

This simple Transformation could be created in code via Dynamic Columns but this shows a no-code solution and has the added benefit that you can easily extend the transformation values by updating the Excel data source.

| |