Match Data

In this article, we will go through the Match Data action step and show you how to set up and use it while you are building your process.

Introduction & Background

You can use the Match Data action step to reconcile data between two tables and identify matched and unmatched records.

Once the step runs it will find all unmatched records in specified tables and will try to match them using the set up rules and conditions.

The Match Data step can find one-to-one, one-to-many and many-to-many matches.

Set up and run the Match Data action step

1. Create a new action step in your process. Choose Add action step option in the group menu.

Choose Match Data type and name your step.

2. Select two tables to match between each other. Click Select table to match.

And choose a table.

Note: you can only select Managed Tables. When the step runs and finds matches it created a system column _MatchID in every table that will hold a match identifier that you can use to later view and analyse matching data.

After you select two tables the step will display them and data in them in a grid format. The grid allows you to view data in tables, sort and filter data by MatchID (matched/unmatched only).

3. Assign columns that represent Amount. This columns will be used to compare found amounts and report on how much unmatched and matched amount was found.

Click on Amount label.

Choose the column that represents amount.

4. Choose columns that are used to uniquely identify records or break down records into groups.

Click on Id label (you can choose as many Id labels as required) and choose a column.

5. Choose a column that represents a date that will be used to group records further (optional).

Click on Date label and choose a column.

6. Save the step.

7. Preview the match results.

Previewing matching results is a way to see what matches will be found when the step runs. This is a very useful functionality if you want to quickly check the output of the step without running it and clearing up data in tables.

Preview will give you matches as if you ran the step but it does not write anything to tables yet. To preview the data click Start run preview button.

After the preview progress is finished you can see the matching results.

Configure matching rules

In the previous section we learnt that the column assignments are used to create matching rules that group records to compare and find matches. You can further configure those rules and supply tolerance settings for amount and dates.

1. Set up Amount tolerance by manually entering a value or linking a data step property.

2. Set up Date difference by manually entering a value or linking a data step property.

3. Add a rule note which will apply to every matched record found by the rule. You can use rule note to apply business logic to your matching.

4. You can specify additional data filtering conditions if you want the matching rules to work on a specific set of data. To do that click Add filter condition

And enter the data filtering conditions

View and Analyse Matching Metadata

You can analyse and view the metadata for each match by querying a global view vRecTableMatch and joining it with your managed table by MatchID.

Here is an example of the SQL query.

Please note that currently you cannot preview a query in the SQL editor, only run a step to import data into the file.

SELECT r.*
, m.StepXianID
, m.StepName
, m.RecMatchID
, m.IsManual
, m.MatchCreatedByID
, m.MatchCreatedByName
, CONVERT(datetime, m.DateCreated)
, m.RuleNote
, m.AStepRecRuleID
, m.RuleName
, m.RuleOrder
, m.AmountDifference
, m.AmountDifferencePropertyID
, m.DateTimeDifference
, m.DateTimeDifferencePropertyID
, m.AStepRecDateScaleID
, m.ScaleName
, m.ScaleDescription
, m.AStepRecRuleConditionID
, m.LeftExpression
, CONVERT(int, m.AStepRecOperatorTypeID)
, m.OperatorName
, m.SQLOperator
, m.RightExpression
, m.RuleConditionOrder
FROM [CO_Bank_Data] r
inner join [vRecTableMatch] m on m.RecMatchID = r._MatchID;

Fields breakdown