Legacy Data Investigation with Alteryx

Investigate a legacy order system and detect data quality issues for cloud migration.

Introduction

30 Second Summary

When a company decides to move their data to the cloud, someone has to figure out what the old system actually contains. The legacy database has no documentation, no one remembers why certain records exist, and hidden data quality issues are lurking everywhere.

In this project, you will build a complete data investigation workflow in Alteryx Designer that reverse-engineers a fictional legacy order management system, detects broken relationships between tables, and produces a migration-readiness assessment ready to hand off to an engineering team.

What You'll Build

You will run your finished workflow and watch it instantly flag orphan records, pivot transaction data into business-rule summaries, and surface sequence gaps that prove the legacy system has integrity issues that must be resolved before any migration begins.

By the end of this project, you'll have:

  • A multi-source ETL workflow that connects four CSV data sources and uses Join tools to detect orphan records at two relationship levels, proving referential integrity failures in the legacy system.
  • A business-rule discovery layer built with Summarize and Cross Tab tools that pivots raw transaction data into readable summaries showing order patterns by customer segment and time period.
  • An anomaly detection branch using Multi-Row Formula to flag sequence gaps in IDs and chronological date anomalies, identifying missing or corrupted records that block migration.
  • A professionally documented workflow with color-coded Tool Containers and annotations that demonstrates BA-level documentation standards suitable for a cloud migration handover.
  • Secret Mission: Build a Fuzzy Match workflow branch to identify potential duplicate customer records and produce a consolidated migration-readiness scorecard.

Are there any prerequisites?

This project assumes strong SQL knowledge (JOINs, GROUP BY, and window function concepts) and comfort with data analysis. No prior Alteryx experience is required.

You will need a Windows PC to run Alteryx Designer. The project uses the free 30-day Alteryx One Platform trial, so there is no cost involved.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Alteryx Designer and Create Legacy Data

Every cloud migration begins with two things: the investigation tool and the system to investigate. Without both, you cannot start discovering how the tables relate or what issues hide in the data.

In this step, you will get Alteryx Designer running through the Alteryx One Platform free trial and create four CSV files that simulate an undocumented legacy order management system. These files contain intentional data quality problems that you will uncover in later steps.

In this step, get ready to:
  • Sign up for the Alteryx One Platform trial and install Alteryx Designer.
  • Create four CSV files representing a messy legacy database.
  • Create and save a blank investigation workflow in Designer.
Sign up for the Alteryx One Platform trial

Alteryx Designer is a visual data analytics tool used by Business Analysts at consulting firms to investigate, blend, and transform data without writing code. You will access it through the Alteryx One Platform 30-day free trial.

  • Open your browser and navigate to alteryx.com/trial.
  • Complete the registration form with your email address and details.
  • Check your inbox for a verification email from Alteryx and click the link to confirm your account.

What does the trial include?

The Alteryx One Platform trial grants you the Workspace Admin and Full User role for 30 days. This gives you access to every tool in Designer, including advanced tools like Fuzzy Match that you will use in the Secret Mission. No license key or payment is required.

  • Once your account is verified, navigate to the Download Center in the Alteryx One Platform.
  • Download and install the Alteryx One app.
  • The Alteryx One app will automatically install the latest version of Designer (2026.1).

Why does it install Designer automatically?

The Alteryx One app is a launcher that manages your license and keeps Designer up to date. You always launch Designer through the Alteryx One app.

  • Press the Windows key to open your search bar.
  • Type Alteryx One and press Enter to launch the app.
  • Sign in with the email and password you used during registration.
  • Alteryx will send an OTP code to your email for verification. Check your inbox and enter the code when prompted.
  • After verification, Designer activates automatically. You should see the Designer canvas appear with the Tool Palette visible on the left side.

Designer not launching?

Make sure you have an active internet connection. The trial requires online activation and cannot be used offline.

If the OTP email does not arrive, check your spam folder. You can also request a new code from the sign-in screen.

Why isn't my Alteryx trial activating after entering the OTP code?

Create the legacy data files

You will now create four CSV files that simulate the tables of a legacy on-premises order management system. The data contains deliberate problems: orphan foreign keys, sequence gaps in IDs, null values, and near-duplicate customer records.

  • Create the project folder by opening Command Prompt (press the Windows key, type cmd, press Enter) and running this command:
mkdir C:\AlteryxProject\data
  • Confirm the folder was created by running:
dir C:\AlteryxProject\data

You should see an empty directory listing confirming the folder exists.

Now you will create the first two data files using Notepad. For each file, you will open Notepad, paste the content, and save with the correct filename.

  • Press the Windows key, type Notepad, and press Enter to open Notepad.
  • Copy the following data and paste it into Notepad:
CustomerID,CustomerName,Segment,Region,Address,Phone,Email,JoinDate
1001,Acme Corporation,Enterprise,Northeast,123 Main St Boston MA,617-555-0101,acme@email.com,2019-03-15
1002,Beta Industries,Mid-Market,Southeast,456 Oak Ave Atlanta GA,404-555-0202,beta@email.com,2019-06-22
1003,Charlie Solutions,SMB,West,789 Pine Rd San Jose CA,408-555-0303,charlie@email.com,2020-01-10
1004,Delta Services,Enterprise,Northeast,321 Elm St New York NY,212-555-0404,delta@email.com,2018-11-01
1005,Echo Partners,Mid-Market,Midwest,654 Maple Dr Chicago IL,312-555-0505,echo@email.com,2020-04-18
1006,Foxtrot LLC,SMB,Southeast,987 Birch Ln Miami FL,305-555-0606,foxtrot@email.com,2021-02-28
1007,Golf Dynamics,Enterprise,West,147 Cedar Blvd Seattle WA,206-555-0707,golf@email.com,2019-09-05
1008,Acme Corp,Enterprise,Northeast,123 Main Street Boston MA,617-555-0101,acme.corp@email.com,2019-03-15
1009,Hotel Group,Mid-Market,Midwest,258 Walnut Way Detroit MI,313-555-0809,hotel@email.com,2021-07-14
1010,India Tech,SMB,West,369 Spruce Ct Portland OR,503-555-0910,india@email.com,2022-01-03
1011,Juliet Brands,Enterprise,Southeast,741 Ash Pl Charlotte NC,704-555-1011,juliet@email.com,2020-08-19
1012,Kilo Manufacturing,Mid-Market,Northeast,852 Poplar Rd Hartford CT,860-555-1112,kilo@email.com,2021-11-30
  • Click File then Save As.
  • Navigate to C:\AlteryxProject\data\.
  • Change the Save as type dropdown to All Files (*.*).
  • Type customers.csv in the File name field and click Save.

Why change Save as type?

Notepad defaults to saving with a .txt extension. Changing to All Files (*.*) ensures Notepad saves the file exactly as customers.csv without appending .txt.

  • Open a new Notepad window (Ctrl+N) and paste the following product data:
ProductID,ProductName,ProductCategory,UnitCost,ListPrice,Supplier,Active
P101,Widget Pro,Hardware,125.00,250.00,SupplierA,Yes
P102,DataSync License,Software,50.00,150.25,SupplierB,Yes
P103,CloudBridge Module,Software,200.00,400.00,SupplierA,Yes
P104,ServerRack Unit,Hardware,250.00,445.00,SupplierC,Yes
P105,NetGuard Firewall,Hardware,110.00,220.00,SupplierB,Yes
P106,Legacy Connector,Software,75.00,175.00,SupplierA,No
  • Save this file as products.csv in C:\AlteryxProject\data\ using the same method (File > Save As, All Files, type the filename).
  • Confirm both files exist by running this in Command Prompt:
dir C:\AlteryxProject\data

You should see customers.csv and products.csv listed in the output.

Now create the two larger transaction tables. These contain the order and line-item records you will join together in Step 3.

  • Open a new Notepad window and paste the following order data:
OrderID,CustomerID,OrderDate,OrderTotal,Status,SalesRep,ShipMethod
5001,1001,2023-01-15,4500.00,Completed,Sarah,Express
5002,1003,2023-01-18,1200.50,Completed,Mike,Standard
5003,1004,2023-02-02,8900.00,Completed,Sarah,Express
5005,1002,2023-02-14,3200.75,Completed,James,Standard
5006,1001,2023-03-01,5100.00,Completed,Sarah,Express
5007,1099,2023-03-10,2750.00,Completed,Mike,Standard
5008,1005,2023-03-22,4100.50,Pending,James,Express
5010,1003,2023-04-05,950.25,Completed,Mike,Standard
5011,1098,2023-04-12,6200.00,Cancelled,Sarah,Express
5012,1006,2023-04-28,1800.00,Completed,James,Standard
5013,1004,2023-05-03,7500.00,Completed,Sarah,Express
5014,1007,2023-05-15,9200.00,Pending,Mike,Express
5015,1001,2023-05-20,3800.50,Completed,Sarah,Standard
5016,1009,2023-02-01,2100.00,Completed,James,Standard
5017,1010,2023-06-10,1450.75,Completed,Mike,Standard
5018,1011,2023-06-22,8800.00,Completed,Sarah,Express
5019,1005,2023-03-15,4600.00,Completed,James,Express
5020,1012,2023-07-01,2900.50,Pending,Mike,Standard
  • Save this file as orders.csv in C:\AlteryxProject\data\.
  • Open a new Notepad window and paste the following order line-item data:
ItemID,OrderID,ProductID,Quantity,UnitPrice,LineTotal,Discount
7001,5001,P101,10,250.00,2500.00,0.00
7002,5001,P103,5,400.00,2000.00,0.00
7003,5002,P102,8,150.25,1200.50,0.50
7004,5003,P104,20,445.00,8900.00,0.00
7005,5005,P101,5,250.00,1250.00,0.00
7006,5005,P102,13,150.25,1950.75,0.25
7007,5006,P103,10,400.00,4000.00,0.00
7008,5006,P105,5,220.00,1100.00,0.00
7009,5007,P102,15,150.25,2253.75,0.00
7010,5008,P104,8,445.00,3560.00,0.00
7011,5008,P101,2,250.00,500.00,0.10
7012,5050,P103,6,400.00,2400.00,0.00
7013,5010,P102,5,150.25,751.25,0.00
7014,5051,P105,10,220.00,2200.00,0.00
7015,5012,P101,7,250.00,1750.00,0.05
7016,5013,P104,15,445.00,6675.00,0.00
7017,5013,P103,2,400.00,800.00,0.05
7018,5014,P104,18,445.00,8010.00,0.00
7019,5014,P105,6,220.00,1320.00,0.10
7020,5015,P101,12,250.00,3000.00,0.00
7021,5015,P102,5,150.25,751.25,0.05
7022,5016,P103,4,400.00,1600.00,0.00
7023,5017,P102,9,150.25,1352.25,0.05
7024,5018,P104,16,445.00,7120.00,0.00
7025,5019,P101,8,250.00,2000.00,0.00
7026,5019,P105,12,220.00,2640.00,0.05
7027,5020,P102,10,150.25,1502.50,0.00
7028,5020,P103,3,400.00,1200.00,0.10
  • Save this file as order_items.csv in C:\AlteryxProject\data\.
  • Confirm all four files exist by running:
dir C:\AlteryxProject\data

You should see all four files listed: customers.csv, orders.csv, order_items.csv, and products.csv.

What is wrong with this data on purpose?

These files simulate a legacy system with real problems you would find during a migration engagement. The issues are intentionally embedded for you to discover using Alteryx.

  • Orphan foreign keys: orders.csv contains CustomerIDs (1099, 1098) that do not exist in customers.csv.
  • Sequence gaps: OrderIDs jump from 5003 to 5005 and from 5008 to 5010, indicating deleted or lost records.
  • Near-duplicate records: CustomerID 1001 (Acme Corporation) and 1008 (Acme Corp) share the same phone number and similar addresses.
  • Orphan line items: order_items.csv references OrderIDs 5050 and 5051 that do not exist in orders.csv.

File saved as .csv.txt instead of .csv?

This happens when you forget to change the Save as type dropdown to All Files (*.*). Delete the incorrect file, reopen Notepad, paste the data again, and make sure to select All Files (*.*) before saving.

Why are my CSV files saving with a .txt extension in Notepad?

Create the investigation workflow

With your data ready, you need a blank Alteryx workflow to build your investigation in. A workflow file (.yxmd) is where you drag tools onto a canvas and connect them to create your data pipeline.

  • Switch back to Alteryx Designer (it should already be open from the Alteryx One app sign-in).
  • Verify that the Tool Palette is visible on the left side of the canvas. If it is not visible, click View in the top menu and select Tool Palette.
  • Click File in the top menu bar.
  • Click New Workflow to create a blank canvas.

Now save this workflow in your project folder so all your work is organized together.

  • Click File then Save As.
  • Navigate to C:\AlteryxProject\.
  • Type legacy_system_investigation in the File name field (the .yxmd extension is added automatically).
  • Click Save.

Your Designer canvas should now show the workflow name in the title bar. The canvas is empty and the Tool Palette is visible on the left with tool categories like In/Out, Preparation, and Join.

Don't see the Tool Palette?

Click View in the top menu and make sure Tool Palette is checked. The palette should appear as a vertical panel on the left side showing categories of tools you can drag onto the canvas.

Why can't I see the Tool Palette in Alteryx Designer?

Your investigation toolkit is ready. Next up, you will connect all four data sources to the canvas and profile the legacy system to see record counts, field types, and null distributions.

Connect Data Sources and Profile the Legacy System

Your Alteryx environment is ready and your legacy data files are in place. Now you need to actually see what is inside those tables.

Before you can discover relationships or data quality issues, you need to get the initial picture: table structures, record counts, and obvious gaps. This is exactly what a BA does when handed an unfamiliar database on day one of a migration engagement. In this step, you will connect all four CSV files to Alteryx and run your first aggregation to profile the data.

In this step, get ready to:
  • Connect four Input Data tools to your CSV files so Alteryx can read the legacy system tables.
  • Attach Browse tools and run the workflow to inspect record counts, field types, and null values.
  • Add a Summarize tool to profile the distinct order statuses in the system.
Add Input Data tools to the canvas

The Input Data tool is how Alteryx reads external files. You need one for each table in the legacy system.

  • In the Tool Palette on the left side of the canvas, expand the In/Out category.
  • Drag an Input Data tool onto the canvas.
  • Click the tool to select it. In the Configuration window on the left, click the file path dropdown and navigate to C:\AlteryxProject\data\customers.csv.
  • Confirm that the First Row Contains Field Names option is checked.
  • Repeat this three more times: drag a new Input Data tool for each of orders.csv, order_items.csv, and products.csv.
  • For each one, select the matching file path and confirm First Row Contains Field Names is checked.

What does this give you?

Each Input Data tool is like running SELECT * on a database table. You now have four parallel streams on the canvas, each pointing at one table in the legacy system. Nothing has run yet though. You need output tools connected before Alteryx processes anything.

Connect Browse tools and run the workflow

A Browse tool lets you inspect the data flowing through a connection. Attaching one to each Input Data tool gives you a window into every table after you run.

  • In the Tool Palette, find the Browse tool under the In/Out category.
  • Drag a Browse tool onto the canvas next to your customers.csv Input Data tool.
  • Click the output anchor (small circle on the right edge) of the customers.csv Input Data tool and drag the connection line to the input anchor of the Browse tool.
  • Repeat for the other three Input Data tools: connect a Browse tool to each one.

Before you run this, what do you think you will see on the connection lines between the tools? Think about how many records each CSV file has.

  • Run the workflow by pressing Ctrl+R.

You should see record counts appear on every connection line: 12 for customers, 18 for orders, 28 for order items, and 6 for products.

  • Click the Browse tool connected to customers.csv to view the data in the Results window at the bottom of the screen.
  • Note the field types displayed in the column headers (e.g., String, Int32, Double) that Alteryx auto-detected from your CSV.
  • Click through each of the other three Browse tools and scan for any obvious nulls or unexpected values in the data grid.

What should I see?

Each Browse shows the full contents of its connected table. The column headers display field names with auto-detected types. The connection lines between tools now show record counts (the number of rows flowing through).

This is your first look at the legacy system. You can already see the shape of the data: how many records, what fields exist, and which columns might have gaps.

Not seeing record counts on the connection lines?

Make sure the connection line between the Input Data tool and its Browse tool is fully attached (the anchors glow when connected). A loose line means the tool has no downstream output and will not run.

If you see a red error icon on an Input Data tool, click the tool and verify the file path in the Configuration window points to the correct CSV in C:\AlteryxProject\data\.

help me troubleshoot why my Input Data tool shows an error in Alteryx

Add a Summarize tool to profile order statuses

Browsing raw data tells you the shape of each table. But you also need to understand the distribution of key values. How many distinct order statuses exist? How are records split across them? A quick aggregation answers this in seconds.

  • In the Tool Palette, expand the Transform category and drag a Summarize tool onto the canvas near your orders.csv Input Data tool.
  • Connect the output anchor of the orders.csv Input Data tool to the Summarize tool's input anchor. This creates a branch: the same output goes to both the Browse tool and the Summarize tool.
  • Click the Summarize tool to open its Configuration window.
  • Click Add and select the Status field. Set its action to Group By.
  • Click Add again and select the OrderID field. Set its action to Count.

What does this Summarize tool do?

This is the Alteryx equivalent of SELECT Status, COUNT(OrderID) FROM orders GROUP BY Status. The Group By on Status creates one output row per unique status value. The Count on OrderID tells you how many orders sit in each status.

Before you run, predict: how many distinct order statuses do you think exist in the legacy system?

  • Run the workflow again with Ctrl+R.
  • Click the Summarize tool to view its output in the Results window.

You should see three rows in the output: Completed, Pending, and Cancelled, each with a count showing how many orders have that status. This is your first aggregation. You now know what states exist in the legacy system.

Summarize tool showing zero rows or an error?

Make sure the connection goes from the orders.csv Input Data tool's output anchor to the Summarize tool's input anchor. If you accidentally connected it from the Browse tool, the Summarize has no data flowing in.

Verify that you selected the Status field (not a similarly named field) and set its action to Group By, not another action type.

help me fix my Summarize tool configuration in Alteryx

You now have a clear picture of the legacy system's shape: four tables, their record counts, their field types, and the distribution of order statuses. Next, you will join these tables together and discover what the legacy system is hiding: orphan records with no matching parent.

Build Multi-Table Joins and Detect Orphan Records

You now have a clear picture of each table's structure, record counts, and status distribution. The next question is harder: how do these four tables connect to each other?

This legacy system has no Entity Relationship Diagram. The only way to discover the relationships is to join the tables and see what matches versus what falls through.

In this step, get ready to:
  • Join the orders table to the customers table and identify orphan orders with no matching customer.
  • Join matched orders to order line items and identify orphan line items with no matching order.
  • Document referential integrity failures at two relationship levels.
Configure the orders-to-customers Join

In a real legacy system, nobody tells you which tables relate to which. You have to hypothesize a relationship (orders reference customers via CustomerID) and test it with a join. Records that match confirm the relationship. Records that do not match reveal broken referential integrity.

  • In the Tool Palette on the left, expand the Join category.
  • Drag a Join tool onto the canvas to the right of your existing tools.
  • Connect the output anchor of your orders.csv Input Data tool to the L (Left) input anchor of the Join tool.
  • Connect the output anchor of your customers.csv Input Data tool to the R (Right) input anchor of the Join tool.
  • Click the Join tool to open its Configuration window on the left.
  • Select Join by Specific Field.
  • In the Left column dropdown, select CustomerID. In the Right column dropdown, select CustomerID.
  • Scroll down to the field list in the Configuration window. Deselect (uncheck) the Right_CustomerID column to avoid a duplicate field in your output.

What is the Join tool doing?

The Join tool combines two inputs based on a common field. It produces three separate outputs:

  • J (Inner Join) contains records that matched on both sides.
  • L (Left Unjoin) contains records from the Left input that did NOT join to records from the Right input.
  • R (Right Unjoin) contains records from the Right input that did NOT join to records from the Left input.

Those L and R outputs are where orphan records appear. This is the equivalent of a SQL LEFT JOIN filtered to NULLs, except Alteryx gives you the unmatched rows directly.

Run the workflow and discover orphan orders
  • From the In/Out category in the Tool Palette, drag a Browse tool and connect it to the J (Inner Join) output anchor of your Join tool.
  • Drag a second Browse tool and connect it to the L (Left Unjoin) output anchor.
  • Drag a third Browse tool and connect it to the R (Right Unjoin) output anchor.

Before you run the workflow, think about this: the orders table has 18 records and the customers table has 12 records. Do you expect all 18 orders to match a customer, or will some fall through?

  • Run the workflow by pressing Ctrl+R.
  • Look at the record counts displayed on each connection line coming out of the Join tool.
  • Click the Browse tool connected to the L output to view its results.

The L output shows 2 records. These are orders whose CustomerID does not exist in the customers table: CustomerID 1099 and CustomerID 1098. These are orphan records.

What are orphan records?

An orphan record is a row that references a parent record that does not exist. In this case, orders reference a CustomerID that has no matching row in the customers table.

This is a critical migration finding. If you move this data to a new cloud database that enforces foreign key constraints, these orphan orders would be rejected on insert. The engineering team must decide whether to delete them, create placeholder customer records, or archive them separately.

  • Click the Browse tool connected to the R output to see customers with no orders.

The R output shows customers that exist in the customers table but have never placed an order. These are not necessarily errors, but they are useful context for the migration team.

Seeing 0 records on all outputs?

  • Check that you connected the correct Input Data tools. The orders.csv tool goes to the L input and customers.csv goes to the R input.
  • Verify the join field mapping. Both dropdowns should show CustomerID. If the field names differ in case or spacing, the join will produce no matches.
  • Make sure your CSV files have headers. The "First Row Contains Field Names" option must be checked on each Input Data tool.

Why is my Join producing zero matches?

Add a second Join for order line items

You have confirmed that orders reference customers. Now test the next level down: do all order line items reference a valid order?

  • Drag a second Join tool onto the canvas, to the right of your first Join.
  • Connect the J (Inner Join) output of the first Join tool to the L (Left) input anchor of the second Join tool.
  • Connect the output anchor of your order_items.csv Input Data tool to the R (Right) input anchor of the second Join tool.
  • Click the second Join tool to open its Configuration window.
  • Select Join by Specific Field. Map OrderID from the Left input to OrderID from the Right input.
  • Drag three Browse tools and connect one to each output anchor (J, L, R) of the second Join tool.

Before you run, think about this: the order_items.csv file has 28 line items. Each references an OrderID. Do you expect all 28 to match an order in the J output of the first Join, or will some be orphaned?

  • Run the workflow by pressing Ctrl+R.
  • Click the Browse tool connected to the R output of the second Join tool.

The R output shows 2 orphan line items. These reference OrderID 5050 and OrderID 5051, which do not exist in the orders table. You now have a two-level orphan analysis proving integrity failures at both the customer-order and order-item relationships.

Why does this matter for cloud migration?

Cloud databases enforce constraints more strictly than many legacy on-premises systems. If the engineering team tries to load this data into a modern database with foreign key constraints enabled, the orphan records will cause the import to fail.

By catching these issues now, you give the team time to decide a remediation strategy before the migration deadline. This is exactly what a BA delivers on day one of a cloud migration engagement.

Seeing more than 2 records in the R output?

  • Confirm the Left input of the second Join connects to the J output of the first Join (not the L or R output). Using the wrong output means fewer orders are available to match against.
  • Verify you are joining on OrderID to OrderID (not CustomerID or another field).

Why does my second Join show unexpected orphan counts?

  • Save your workflow by pressing Ctrl+S.

Confirm the record counts visible on your connection lines. The L output of the first Join shows 2 orphan orders. The R output of the second Join shows 2 orphan line items. The J outputs show the successfully matched records flowing through.

You have proven that this legacy system has broken referential integrity at two levels. Next up, you will use aggregation and pivoting tools to discover the business rules hidden inside the matched data.

Aggregate, Pivot, and Discover Business Rules

The joins you built in the previous step revealed which records connect across tables and which are orphaned. But knowing which records match is only half the picture.

The engineering team preparing the migration needs to understand the patterns hiding inside those matched records. How are orders distributed across customer segments? What does revenue look like by product category? In this step, you will use the Summarize and Cross Tab tools to aggregate raw transaction data into business-rule summaries that must be preserved when the system moves to the cloud.

In this step, get ready to:
  • Aggregate order volume and revenue by customer segment and date.
  • Pivot the aggregated data into a readable summary using Cross Tab.
  • Summarize product category performance from the order-items relationship.
Summarize order data by segment and date

Your first Join tool's J output contains every order successfully matched to its customer record. That stream includes both order fields (OrderDate, OrderTotal) and customer fields (Segment). You will aggregate this stream to see how order volume and revenue break down by customer segment over time.

  • In the Tool Palette on the left, expand the Transform category.
  • Drag a Summarize tool onto the canvas to the right of your first Join tool.
  • Connect the J (Inner Join) output anchor of the first Join tool to the input of this new Summarize tool.
  • Click the Summarize tool to open the Configuration window on the left.
  • Select the Segment field and set its action to Group By.
  • Select the OrderDate field and set its action to Group By.
  • Select the OrderID field and set its action to Count.
  • Select the OrderTotal field and set its action to Sum.
  • Connect a Browse tool to the output of this Summarize tool.
  • Run the workflow with Ctrl+R.
  • Click the Browse tool connected to the Summarize output to view the results.

You should see rows grouped by customer segment and order date, with a count of orders and the total revenue for each combination.

What does this output tell you?

Each row represents one segment-date combination. The Count_OrderID column shows how many orders that segment placed on that date. The Sum_OrderTotal column shows the revenue.

This is your first business-rule discovery: you can now see which segments generate the most orders and revenue. After migration, these numbers must still match. If they change, the migration introduced a defect.

Seeing zero rows in the output?

Make sure you connected the Summarize tool to the J output of the first Join tool, not the L or R output. The J output contains the matched records with both order and customer fields.

Also confirm that the Segment field appears in the field list. If it does not, the Join tool may have deselected it. Go back to the Join Configuration and ensure the Right input's Segment field is checked.

Help me troubleshoot my Summarize tool configuration.

Pivot the data into a readable summary with Cross Tab

The Summarize output has one row per segment-date combination. That is accurate but hard to scan. A pivot table with segments as rows and months as columns makes the pattern instantly visible.

  • From the Preparation category in the Tool Palette, drag a Formula tool onto the canvas.
  • Connect the output of your new Summarize tool (the one you just configured) to the input of the Formula tool.
  • Click the Formula tool to open its Configuration window.
  • In the Output Column dropdown, select Add Column and name the new field OrderMonth.
  • In the expression editor, enter the following expression:
Left([OrderDate], 7)

What does this expression do?

The Left() function extracts the first 7 characters from each OrderDate value. Since dates are formatted as 2023-01-15, the first 7 characters give you 2023-01. This groups all orders in the same month together for the pivot.

Now add the Cross Tab tool that will turn the vertical date values into horizontal column headers.

  • From the Transform category, drag a Cross Tab tool onto the canvas.
  • Connect the output of the Formula tool to the input of the Cross Tab tool.
  • Click the Cross Tab tool to open its Configuration window.
  • Set Group data by these values to Segment.
  • Set Change Column Headers to OrderMonth.
  • Set Values for New Columns to Count_OrderID.
  • Set the Method for Aggregating Values to Sum.
  • Connect a Browse tool to the output of the Cross Tab tool.
  • Run the workflow with Ctrl+R.
  • Click the Browse tool connected to the Cross Tab output.

Before you look at the results, think about what you expect to see. What would a pivot table with segments as rows and months as columns look like?

You should see a grid where each row is a customer segment (Enterprise, Mid-Market, SMB) and each column is a year-month value (2023-01, 2023-02, etc.). The cells contain order counts for that segment in that month.

Why does Cross Tab not add a prefix to the column values?

When Sum is the only aggregation method selected, the Cross Tab tool does not prepend an abbreviation to the column headers. That is why your columns read 2023-01 instead of Sum_2023-01. This keeps the output clean and ready for handover.

Cross Tab output shows only one column?

Confirm that you set Change Column Headers to OrderMonth (the field created by the Formula tool), not OrderDate. Using the full date would create one column per individual date instead of grouping by month.

Help me debug my Cross Tab configuration.

Summarize product category performance

The pivot table answers "which segments buy when." The next question is "which products drive the business." Your second Join tool's J output contains order line items matched to their parent orders. You will aggregate that stream by product category to see volume and revenue per category.

  • Drag a new Summarize tool from the Transform category onto the canvas.
  • Connect the J (Inner Join) output of the second Join tool to the input of this Summarize tool.
  • Click the Summarize tool to open its Configuration window.
  • Select the ProductCategory field and set its action to Group By.
  • Select the Quantity field and set its action to Sum.
  • Select the LineTotal field and set its action to Sum.
  • Connect a Browse tool to the output of this Summarize tool.
  • Run the workflow with Ctrl+R.
  • Click the Browse tool to view the product category summary.

You should see two rows: one for Hardware and one for Software. Each row shows the total quantity sold and total revenue for that category.

Why are these numbers critical for migration?

These totals represent baseline business rules. Hardware and Software each have a known total quantity and revenue in the legacy system. After the data migrates to the new cloud platform, the engineering team must re-run the same aggregation and confirm the numbers match exactly.

If the numbers change, the migration introduced a defect. Your workflow just created the benchmark that proves the new system is correct.

ProductCategory field not appearing in the list?

The ProductCategory field comes from the order_items.csv data that flows through the second Join's Right input. Confirm that you connected this Summarize tool to the J output of the second Join tool (the one that joined orders to order items), not the first.

Help me find the ProductCategory field in my Summarize tool.

You now have aggregated summaries that reveal how this legacy system actually works. The Cross Tab shows ordering patterns by segment and month. The product summary shows revenue distribution by category. These are the baselines the engineering team will use to validate the migrated data. Next up, you will detect sequence gaps and date anomalies that prove records were deleted or corrupted in the legacy system.

Detect Sequence Gaps and Date Anomalies

Your joins uncovered orphan records, but there is another category of data quality issue hiding in the legacy system. Sequential IDs with gaps indicate records that were deleted or lost. Date fields that contradict their chronological order suggest application bugs or manual data entry errors.

Both of these are migration blockers that must be cataloged before any data moves to the cloud. The Multi-Row Formula tool lets you compare each row to its neighbor, exactly like a SQL window function, to flag these anomalies automatically.

In this step, get ready to:
  • Sort order data and calculate gaps between sequential IDs using Multi-Row Formula.
  • Filter to isolate records where gaps prove missing data in the legacy system.
  • Detect date anomalies where order timestamps contradict their ID sequence.
Sort orders and calculate ID gaps

If the legacy system deleted records, the auto-incrementing OrderID column will have gaps. To find them, you need each row to know what the previous row's ID was. That is exactly what Multi-Row Formula does.

  • Start a new branch from the orders.csv Input Data tool. Drag a Sort tool from the Preparation category in the Tool Palette onto the canvas.
  • Connect the output anchor of the orders.csv Input Data tool to the input anchor of the Sort tool.
  • In the Configuration window for the Sort tool, click Add and select the OrderID field with Ascending order.
  • Drag a Multi-Row Formula tool from the Preparation category onto the canvas and connect the Sort tool's output to it.
  • In the Configuration window, select Create New Field and name it ID_Gap with Type set to Int32.
  • Set Num Rows to 1.
  • In the expression editor, enter this expression:
[Row+0:OrderID] - [Row-1:OrderID]
  • Set Values for Rows That Don't Exist to Set to Values of Closest Valid Row.
  • Connect a Browse tool to the Multi-Row Formula output.
  • Run the workflow with Ctrl+R.
  • Click the Browse tool and examine the Results window. You should see a new ID_Gap column. Most rows show 1 (consecutive IDs), but some show 2 or higher (missing IDs in between).

What does this configuration do?

The Sort tool arranges all orders by their ID in ascending order so each row's neighbor is the next sequential record.

The Multi-Row Formula then subtracts the previous row's OrderID from the current row's OrderID. A result of 1 means the IDs are consecutive. Anything higher means records are missing from the sequence.

The "closest valid row" setting handles the first row (which has no previous neighbor) by using its own value, producing a gap of 0 instead of a null error.

Seeing null values in the ID_Gap column?

Make sure you set "Values for Rows That Don't Exist" to "Set to Values of Closest Valid Row" instead of "Null". Re-open the Multi-Row Formula configuration and check the dropdown at the bottom.

Also verify the expression references the correct field name. It must be OrderID (case-sensitive, no spaces).

Why is my Multi-Row Formula producing nulls or errors?

Filter to isolate sequence gaps

Now that every row has its gap calculated, you need to isolate only the rows where a gap actually exists. A Filter tool separates matching records from non-matching ones into two streams.

  • Drag a Filter tool from the Preparation category onto the canvas.
  • Connect the Multi-Row Formula tool's output to the Filter tool's input.
  • In the Configuration window, select Basic Filter and set the condition:
[ID_Gap] > 1
  • Connect a Browse tool to the True (T) output anchor of the Filter.
  • Connect a Browse tool to the False (F) output anchor of the Filter.
  • Run the workflow with Ctrl+R.

Before you check the True output, predict: how many sequence gaps do you think exist in 18 orders numbered between 5001 and 5020?

  • Click the Browse tool connected to the True output. You should see records where ID_Gap is 2, proving that OrderIDs 5004 and 5009 are missing from the sequence.

What do these gaps mean for migration?

The True output contains records that immediately follow a gap. When OrderID jumps from 5003 to 5005, the record for 5005 appears in the True output with an ID_Gap of 2.

These gaps prove the legacy system deleted or lost records (5004 and 5009). The engineering team needs to know: were those orders archived elsewhere, or is the data permanently gone? That question must be answered before migration.

Filter True output is empty?

Check that your Sort tool is sorting by OrderID in ascending order. If the data is unsorted, the gap calculations will be meaningless.

Verify the Filter condition uses [ID_Gap] > 1 (with square brackets around the field name). The field name must match exactly what you entered in the Multi-Row Formula.

Why is my Filter not catching any sequence gaps?

Detect date order anomalies

Sequence gaps tell you records are missing. Date anomalies tell you records are wrong. If OrderID 5016 has a date earlier than OrderID 5015, something went wrong in the legacy application. Either an order was backdated manually, or the system clock was incorrect.

  • Drag a second Multi-Row Formula tool onto the canvas.
  • Connect the Sort tool's output to this new Multi-Row Formula's input. You are branching a second stream from the same Sort output.
  • In the Configuration window, select Create New Field and name it Date_Order_Valid with Type set to Bool.
  • Set Num Rows to 1 and enter this expression:
[Row+0:OrderDate] >= [Row-1:OrderDate]
  • Set Values for Rows That Don't Exist to Set to Values of Closest Valid Row.
  • Drag a Filter tool onto the canvas and connect the second Multi-Row Formula's output to it.
  • Set the Filter condition to:
[Date_Order_Valid] = False
  • Connect Browse tools to both the True and False output anchors of this Filter.

What does this branch detect?

This expression checks whether each order's date is on or after the previous order's date (when sorted by ID). If a higher OrderID has an earlier date, the result is False. That means the order was either backdated manually or created during a system clock issue.

The Multi-Row Formula tool can only update one field per tool instance. That is why you need a separate Multi-Row Formula for each calculation rather than combining them into one tool.

Before you run the workflow, predict: do you think date anomalies will be more or less common than sequence gaps in this dataset?

  • Run the workflow with Ctrl+R.
  • Click the Browse tool connected to the True output of the second Filter. You should see records where Date_Order_Valid is False. OrderID 5016 appears because its date (2023-02-01) is earlier than the preceding record's date (2023-05-20), proving a backdated entry.

The connection lines on your canvas now display record counts for each output. The first Filter's True output shows the sequence gap records. The second Filter's True output shows the date anomaly records.

Second Multi-Row Formula showing errors?

Make sure you connected the second Multi-Row Formula to the Sort tool's output, not to the first Multi-Row Formula's output. Both Multi-Row Formula tools should branch from the same Sort.

Verify that the field name Date_Order_Valid in the Multi-Row Formula matches exactly what you reference in the Filter condition. Underscores and capitalization matter.

Why is my date anomaly detection not working in Alteryx?

You have now built a complete anomaly detection branch that automatically flags missing records and date inconsistencies. These findings, combined with the orphan records from your joins and the business rule summaries from your aggregations, give the engineering team a full picture of what must be fixed before migration. Next up, you will package all of this into a professional deliverable with documentation that an engineering team can act on.

Secret mission

Fuzzy Match Duplicates and Migration Scorecard

Real legacy systems always have duplicate records that simple exact-match joins cannot detect. Can you use the Fuzzy Match tool to find near-duplicates and produce a unified migration-readiness scorecard summarizing every data quality finding?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally with a free trial, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Alteryx One app and Designer (installed on your Windows machine).
  • Local project folder C:\AlteryxProject\ containing CSV data files and your workflow.
  • Alteryx One Platform trial account (expires automatically after 30 days, no charges).

Keep everything running

No action needed. Choose this if you are still actively building workflows or want to explore other Alteryx tools.

  • Your trial remains active for 30 days from signup. You can continue building workflows and exploring tools in Designer.
  • Your workflow file (legacy_system_investigation.yxmd) and CSV data remain in C:\AlteryxProject\.
  • After the trial expires, you can still sign in to the Alteryx One Platform, but you will not be able to access your cloud workflows or use Designer.

Pause - I'll come back to this later

Close Designer to free up system memory. Your files stay on disk and your trial countdown continues in the background.

  • Close Alteryx Designer and the Alteryx One app.
  • Your workflow and CSV files remain untouched in C:\AlteryxProject\.
  • Your trial continues its 30-day countdown. No resources are consumed while the app is closed.

Delete - I don't want to use this again

Remove all project resources and the Alteryx application from your machine.

  • Delete the project folder by opening a Command Prompt and running:
  • Uninstall Alteryx Designer and the Alteryx One app by typing Add or Remove Programs in the Windows taskbar search box.
  • In the list of installed apps, find the Alteryx products (Alteryx One, Alteryx Designer).
  • Select each Alteryx product and click Uninstall.
  • Your trial account will expire automatically after 30 days. No manual account deletion is required.
rmdir /s /q C:\AlteryxProject

Nice Work!

Nice Work!

Nice work! You just built a production-quality data investigation workflow that reverse-engineers a legacy order management system and delivers a documented migration-readiness assessment.

This is exactly what a Business Analyst delivers on day one of a cloud migration engagement.

You've learned how to:

  • Set up Alteryx Designer via the Alteryx One Platform free trial and create simulated legacy database tables with intentional data quality issues for investigation.
  • Build multi-table Join workflows that detect orphan records at two relationship levels (customer-order and order-item), proving referential integrity failures in the legacy system.
  • Use Summarize and Cross Tab tools to aggregate transaction data and pivot it into business-rule summaries showing order patterns by customer segment and time period.
  • Apply Multi-Row Formula to detect sequence gaps in OrderIDs and chronological date anomalies, identifying missing or corrupted records that block migration.
  • Secret Mission: Used Fuzzy Match to identify duplicate customer records and produced a unified migration-readiness scorecard quantifying all data quality findings for engineering handover.

Ready to quiz yourself?