I need to write a formula to solve the below scenario.
I am an auditor performing intercompany transactions testing:
I have two files:
1. Receipts
2. Payments
The Manual way I do:
In the Payments file I have three columns and in the receiver's file I have three columns.
1. Account Number
2. Amount
3. Bank's weird comment containing the receiver's account number. My goal is to match $ 1,000,000 sent from account A in the payments file to account B in the receiver's file using this comment column.
Bank's comment usually look like this : #p9e29u rdshis fsuf sufw 9279572947 sgfu wqfs fs fshfgis fgsoif saf #hhaifgusa fguosa ou h 25472905479 ajfisfsai fyh 0264290872) bold underline is the receiver's account number I made for your reference banks does not bold/underline it). The problem is sometimes the comment can have other related account number which is not a receiver's account number.
Solved! Go to Solution.
@Vijay_Vittal_11 can you provide more details please:
What is the customer account number length and is it a fixed length (i.e. 10 characters).
Are there unique feature that identify an account number from just some random series of string (i.e first 5 digits of a 10 digits number are always 43678 in)
Are the other numbers sometime have the same length as the customer account numbers.
To extract the receiver's account number from the bank's comment column in both the Receipts and Payments files, you can use Alteryx's Regex tool.
Here's an example workflow:
Use an Input Data tool to read in the Payments file and select the three columns: Account Number, Amount, and Bank's Comment.
Use a Regex tool to extract the receiver's account number from the Bank's Comment column. Use the following Regular Expression:
(?<=\s)\d{10}(?=\s)
This regex looks for 10-digit numbers surrounded by spaces.
Use a Select tool to rename the new column created by the Regex tool as "Receiver's Account Number".
Use a Filter tool to remove any rows where the Receiver's Account Number is null or empty.
Use a Summarize tool to group the data by the Receiver's Account Number, and calculate the sum of the Amount column.
Use an Output tool to write the data to a new file.
Repeat steps 1-6 for the Receipts file.
Use a Join tool to combine the two datasets based on the Receiver's Account Number.
Use a Filter tool to remove any rows where the sum of the Payments and Receipts do not match.
Use an Output tool to write the final data to a new file.
This workflow should extract the receiver's account number from the bank's comment column and match the payments to the receipts based on the account number.
Can you share some sample rows of data?