Insights

Transforming and Grouping your Data Using Launchpad

by Lance Abbrederis on Aug 12, 2022

Recently, Calibrate Analytics introduced several exciting new Launchpad features aimed at helping you go above and beyond with transforming, organizing and grouping your data. These features enable you to modify data directly in Launchpad via the source of your choice which can then be used in your analytical processing and dashboarding.

Since data from multiple sources comes with different schemas and formats, every dataset requires transformations before utilizing BI and analytical tools. Functions, custom fields and lookup tables help to compile, convert, reformat, and cleanse the data before it is fed into your target destinations.

Functions

You can use functions to convert data according to your business requirements or to reformat already-converted data to a standard format for compatibility. You can also use functions to cleanse irrelevant data from the datasets or to translate data where necessary.

Functions are specific actions that can be applied to anything ranging from text strings (i.e. search & replace) to numbers (i.e. round a number up) to date functions (i.e. extract the year from a date value). In addition to predefined functions, Launchpad also offers advanced users the capability to write custom functions using a javascript-like syntax.

Functions can be chained together if you need to perform a series of actions on a field. For example, if you need to first lowercase data in a field before performing a search & replace. Multiple functions applied to a field are executed in order from left to right. For maximum flexibility, functions also work in conjunction with custom fields and lookup tables.

Custom Fields

Custom fields are fields that you can add to your data set in addition to the field supplied from the data source. Custom fields are “derived” fields, meaning they are typically generated from an original, existing field. For example, you could create a custom field called “campaign_name_2” which was derived from an original “campaign_name” field.

You can select the field type of your choice, usually basing it on the field type of the original field. For example, if the parent field was a string you probably want your custom field to be a string. You may also override this and select the type of your choice. This is useful in cases where you might need an IF/ELSE evaluation against a string and you wish to return a number as the final output. In such a case you would select the field type of ‘integer’ for the custom field.

Custom fields are also useful for when you need to retain the original field value. They also work in conjunction with functions and lookup tables.

Lookup Tables

Lookup tables are useful for replacing source data with the actual data as you want it to appear in the data warehouse. For example, a lookup table could be used to replace a zip code with a full address, or conversely, to replace a full address with a zip code.

Creating a lookup table in Launchpad is as simple as creating a spreadsheet via the Launchpad interface. The first row becomes column headers (titles) and each additional row is treated as the lookup table data.

Lookup table actions can be applied using a function attached to a specific field in the dataset. To apply the lookup table function, select the lookup tables column value to match against your field value and select the replacement value.

Ultimately the lookup table feature in Launchpad is used to transform the input data into a more desirable output format which helps get a jump on your data organization and cleansing.

Data Previews

Data previews make it easy to explore the data as you prep it. You can use the previews to test combinations of functions, lookup tables and custom fields to be sure you will get the desired output. Data previews are available for all synchronous source types in Launchpad.

Apart from confirming the output of your transformations against a sample of your data set, running a data preview is also a great way to identify issues within source set-up or column mapping.

Conclusion

Since data from multiple sources comes with different schemas and formats, every dataset requires transformations before utilizing BI and analytical tools. Functions, custom fields and lookup tables help to compile, convert, reformat, and cleanse the data before it is fed into your target destinations.

Launchpad is our powerful and easy to use one stop shop to transfer data from an array of domains to destinations of your choice. It helps customers easily access their data and transfer it to their preferred data warehouse where it can then be transformed, analyzed and visualized.

Find out more

Share this post: