DESPERATE FOR HELP: FORMULA TOOL HELP

Vijay_Vittal_11

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.

Custom Tools

Accepted answers

BRRLL99

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.

All comments

Tam

@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.

BRRLL99

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.

Raj

Can you share some sample rows of data?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703