Skip to main content

Varicent ELT Help Center

Record ID and adjustment

Important

Currently, this Blueprint is only available to Varicent ELT and Varicent users. For more information, please contact the Varicent Support team.

In this pipe, we will be adding unique transaction IDs to two data sets that do not have unique identifiers, and then tracking adjustments made to the transactions at source.

This pipe expects two input data sets:

  • Prior Period Transactions.csv (Data): This is the prior period transaction data. It lacks a unique single column ID for the transactions.

  • Current Period Transactions.csv (Data): This is the transaction data for the current period. It contains adjustments to some records from the prior quarter.

Workflow

The pipe starts by importing the current period and prior period transactions that we want to track adjustments from. Current Period Transactions.csv (Data) Prior Period Transactions.csv (Data).

First, we extract the quarter (our time length of interest) using Varicent ELT's Text Part tool.Extract Quarter as Text (Text Part) Extract Quarter as Text (Text Part).

The Add Unique ID (Record ID) and Change ID to Text (Change Type) tools add a unique ID and change the resulting type to Text so that an enhanced RecordID can be created afterwards using Create Enhanced ID (Formula). This is a formula tool that makes our final unique ID by concatenating the transaction ID we just created with the quarter. This results in a column called "Record ID", with a format like: 2017Q3 

Next, the two data sets are joined together and find which transactions from the prior period have been adjusted, using Varicent ELT's Combination Matcher tool. This tool matches up the current period transactions with the prior period, using whichever columns you define as "match" columns. Identify Adjustments (Combination Matcher) Matching up the current period transactions with prior period in order to find adjustments leads to the following matches:  

  • Transaction 2017Q22 (Current Period) is an adjustment to transaction 2017Q20 (Prior Period)

  • Transaction 2017Q23 (Current Period) is an adjustment to transaction 2017Q23 (Prior Period)

  • Transaction 2017Q27 (Current Period) is an adjustment to transaction 2017Q26 (Prior Period)

  • Transaction 2017Q28 (Current Period) is an adjustment to transaction 2017Q28 (Prior Period)

Adjusted Records (Filter) filters for the adjusted transactions for further treatment and New Records (Filter) filters for the new transactions for further treatment.  

Tag New Records (Formula) Tag Adjusted Records (Formula) add a column describing whether the transaction is net-new or an adjustment.  

Drop New Record ID (Drop) Apply Prior Period Record IDs (Rename) are used to remove the recordID from the current period transactions and instead apply the appropriate prior Record IDs (MatchID) to the adjusted transactions.  

Finally, the Full Current Period Data Set (Union) tool merges the current period transaction data set together such that the adjusted transactions now have the correct previous period RecordIDs and new transactions have new unique RecordIDs.  

The final tool, Export exports the data out for download and use in the rest of your transaction processing workflow.