How to cleanse and transform data with Toad Data Point

Toad Data Point has a powerful feature that makes it easy to cleanse and transform data for the best analysis. You can also save the data locally on your workstation (referred to as local storage) or you can share it with others in your organization using Quest Toad Intelligence Central.

This blog will focus on these areas:

• What is transform and cleanse
• A working example of data scrub
• How to add additional columns
• Saving and sharing cleansed/transformed data

What is cleanse and transform?

The ability to cleanse and transform data is critical for any serious data preparation initiative. This is because data that’s stored in multiple repositories may be in different and inconsistent. Cleanse and transform data functions make columns consistent with activities such as spelling out state codes, changing formats, splitting columns, adding columns and performing calculations. It’s essentially a “data scrub.”

I’ve found that additional columns are helpful if there is some common math that is usually associated with the data, such as a running total. I always recommend replacing null values with zeros. This feature of Toad Data Point can easily do that, as well as set up new columns (and hide the original) where database SQL functions are used. Ultimately, this can make using the data easier for an end user.

A working example of a data scrub

In this section I will transform data and illustrate fixing some bad email addresses.

Example data scrub to fix bad email addresses

This simple query pulls up 14 rows of my test data. Notice the email addresses are a mess. Also notice the COMM field has some null values and some zeros as well. There are other things to fix here but I’ll keep the example short for purposes of this blog.

I start with a simple SQL query, then right click on the data grid and select 'Send to' à'Transform and Cleanse.

Starting cleanse and transform data

This is Toad Data Point Workbook. I have already built the simple query using Query Builder and have added a step. Toad Data Point knows what I can do with a data grid, and I'll choose 'Work Step' and 'Dimensional View.'

Transform and cleanse panel in Toad Data Point

This panel shows a lot of useful information. Along the bottom, you see useful export and save buttons for Local Storage and Toad Intelligence Central (Publish Data), and Automate (to apply these transformations repeatedly to other similar data/future updated data using Automation).

Transform data and cleanse steps

Transformation and Cleanse works by adding steps or rules that will transform data. To see and edit these rules, click on the 'Steps' tab near the upper right corner. You will see the steps populate as we start to transform data and complete the alterations. These rules can be saved so Automation can reapply them repeatedly if necessary.

Find and Replace Panel

Right click on the EMAIL column and pick 'Replace' or click on the binocular button and make sure EMAIL is the column.

Transform and cleanse find and replace function

We will first get rid of the double quotes that appear on some of the email addresses. Fill in the Find with a double quote and nothing in the Replace field. Notice that Toad Data Point shows the number of rows affected.

Click on 'Apply Rule' and you will see the 'Steps' column on the right.

Building cleanse and transform data steps

Next, we will add a .com to the email addresses missing a .com at the end. I have a request into Quest support to add a global character to do this but for now, I do this in 2 steps. I find all the 'Acme.' Domain and add a '.com' to them. This adds an additional .com to the addresses that already had one. THEN I use find and replace yet again to find all the '.com.com' and simply change them back to a simple '.com'.

Use find and replace to cleanse email addresses

How to add columns

I will show two ways to fix null values, and will add a column Total_Compensation (a total of Salary and Commissions).

Using transform and cleanse to fix null values

I can think of a couple of ways to fix null values. Using Find and Replace again, notice it will show nulls, blanks, zeros and other values. To take this next step to transform data, what do you want to do?

Here we could simply change the nulls to zero. When doing the Total_Comp new column, I will show you the null value function. By fixing these nulls here, we don't have to worry about them in the next step. If you get these steps out of order, simply drag and drop them in the 'Steps' panel to rearrange them.

Using the calculated column function

Now we will use the 'Calculated Column' button (the second button on the tool bar or right mouse click from the SAL field). I call this the 'add column' button.

We can simply click on the column names (with columns highlighted on the left) picking the SAL field, clicking on the '+' then clicking on the COMM field.

Adding a new column with a calculated value

If we wanted to fix the COMM field's null values with a function, do the above, then highlight the COMM field and select Control Flow Functions. The function you want here is IFNULL. Make it look like the above panel and this too will work.

Is one better than the other? Not really. I'm used to doing things with SQL so I prefer the functions as I'm a bit "old school."

Seeing the calculation in the steps

Slide over and make sure your math is correct on the new column and we are good here.

Save and retrieve the scrubbed data

You can save the transformed data – and any data in a data grid – to a new data store in either your Local Storage (for your use) or to Toad Intelligence Central (for everyone's use).

Save to Local Storage

Click the Save to Local Storage button along the bottom and name it 'EMP_CLEANSE'. I'm putting it in the EMP_DATA database with the other related data.

To share with others, click the Publish button and this will save your transformed data to Toad Intelligence Central.

New data store with transformed data

Now, access the newly-transformed data as you would any other data store!

Toad Data Point can clean and transform data, merge data, add columns/split columns and more. This feature makes it easy for business intelligence users to save a set of common steps, so they don’t have to fix things with each report. Common calculations can also be made available as an additional column. The cleansed and transformed data can be saved and shared with others in the organization.

Related links:

Blog: How to use Quest® Toad® Data Point to improve data integrity

Blog: How to use Toad Data Point to solve data preparation issues

Blog / video: What is data preparation and why is it key to the business analyst?

Webinar: Answering Your Top Five Questions About Toad Data Point Automation

Ebook: The Four Roadblocks of Data prep

Visit Quest for data prep and provisioning with Toad Data Point and Toad Intelligence Central

IDC Datasheet: Quest Software Acquires erwin Inc.

Visit Quest erwin, for enterprise data governance

Blog: How to prepare data for machine learning

Blog: Easy data prep: Toad® Data Point v5+ new features

Quest blog: Data prep and Analysis: Using Data Efficiently in the Era of Dark Data

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

Dan Hotka has several course offerings that use Toad and Toad Data Point.