Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Our objective is to compare two tables with the same structure before putting the process into production (an Alteryx workflow for example) that feeds the new table to replace the old one.
To illustrate this use case, I will use the following two tables:
Production table:
It is important to have a unique identifier in both tables. If you don't have one you can create one with the Record ID tool.
Dev table:
The elements highlighted in yellow are the elements that are not identical to the production table.
Preparation with Designer
I use two tables from my SQL Server database in this demonstration:
I then tag the two environments with a Formula:
This will allow me to identify the source of the data.
And with a Union to follow, here we are:
The data structure is as follows (Prod and Dev data have been stacked):
The magic pivot
A - The theory
To understand this step, here is a diagram of the data structure before and after.
The input data...
...can be summarized as follows:
And the expected output data...
... follow the pattern below:
B - Let's do it now!
In practice, we need two tools: Transpose and Cross Tab
We set the ID and environment to rotate the rest of the data as follows:
Our data has this structure now:
For the pivot table, we keep ID and Name as columns.
The environments (Dev and Prod) will be used as headers while the values (in the value column) will feed the heart of the table:
Setting up the control
A sorting of the data for more clarity and a filter checking if the Dev data are equivalent to the Prod data and that's it!
This gives you the data without regressions (T output) and the errors (F output).
A self-service application ?
Why not share this workflow freely on Server?
Each user (whether they use Alteryx Designer or not) could then do their own non-regression tests.
Think of the developers who change a business rule in a flow, what about the business users who have to validate the consolidation? The use cases are numerous.
Analytic App transformation
The first step is to transform this workflow into an analytical application to enable interaction.
We add two Text Box tools:
The Action tool updates the name of the database table:
Now on the Server
Once published, the application can be placed in a District. Mine is called Self Service by IT.
The public application is executable by all:
When launching, I can choose the tables to compare:
And see the gaps on the Gallery:
This should help you validate the compliance of your data and also enrich the services your Gallery offers.
The workflow is attached.
