Data Preparation Is Easy with Alteryx

Append Leading Zeros Splash

Without support from I.T., analysts increasingly need to perform data preparation tasks of varying complexity in order to wrangle data into shape for current analytic needs.  Using Alteryx Designer, many such tasks are simple and intuitive.  Let’s consider an example.

Append Leading Zeros Workflow

For the completed Alteryx workflow sample published in Alteryz product documentation, assume that, due to an unfortunate conversion of the zip (code) field from string to integer data type, all leading zeros were lost.  When that occurred, the valid zipcode  “01002” became the erroneous integer 1002.  Here is an example.

Append Leading Zeros Unimproved

Let’s fix it.  To complete this, we will do four things:

  1. Add an Input Data tool to connect to the corrupted data as our input
  2. Add and connect a Formula tool to the data input, and write a simple formula
  3. Add a Browse tool to enable viewing of our results (for demo purposes)
  4. Run the workflow to observe and validate actual results.

Step 1 (Input Data):  In Alteryx (v10) Designer, if you don’t see the tool you want, type it into the ‘Search All Tools’ window (upper left).  In this case, when we drag the Input Data tool onto the canvas,   we see the tool configuration dialogue, in which we connect to the source file or database, as shown below.

Append Leading Zeros Input Data

Step 2 (Formula):  Next, we drag the Formula tool onto the canvas just to the right of Input Data and it connects to it as shown below.  In order to append a leading zero, of course, we need to locate the zip field and change it’s data type to String (V_String), which we will do now.  Next, just below there in the Formula configuration windows, we click on the Functions tab and, with a little research, we note the PadLeft function, note also the explanation provided in the grey space below it, then click on it, enter the targetted output field (zip), length (5) and what to pad (“0”) onto it’s left enough times to make a full 5 digits.

Append Leading Zeros Formula

Step 3 (Browse):  Let’s validate our formula now.  In the screenshot below, we drag the Browse tool onto the canvas, connecting it to output from the Formula tool.

Step 4 (Run Workflow):  When ready, we note the green arrow “Run Workflow” icon just below and to the right of the Help menu,  click on it, and our little transformation is executed.  To view the results, in the screenshow below, we ensure that, in the lower right, “Data” is selected, not “MetaData”.

Happily, we note that the our zip field now has leading zeros as we intended.

Append Leading Zeros Browse

What else does it do for Analysts?  Plenty…

Not surprisingly, Alteryx has many tools for data preparation, analysis and presentation, including aggregation, pivotting, complex joins, pattern identification, fuzzy matching, creation of MD5 Hashes, advanced geographic mapping, integrating third party data, xml parsing, …and predictive analytics!

What I think stands out with Alteryx is the ease with which business analysts (notice I did not say engineers or data scientists) can easily master many of these capabilities and thereby increase their value added.

It’s even kind of fun.  To see for yourself or download a free trial, click here.


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 )

Facebook photo

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

Connecting to %s