Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DESPERATE FOR HELP: FORMULA TOOL HELP

Vijay_Vittal_11
6 - Meteoroid

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.

 

 

 

 

3 REPLIES 3
Tam
9 - Comet

@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
11 - Bolide

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:

  1. Use an Input Data tool to read in the Payments file and select the three columns: Account Number, Amount, and Bank's Comment.

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

  3. Use a Select tool to rename the new column created by the Regex tool as "Receiver's Account Number".

  4. Use a Filter tool to remove any rows where the Receiver's Account Number is null or empty.

  5. Use a Summarize tool to group the data by the Receiver's Account Number, and calculate the sum of the Amount column.

  6. Use an Output tool to write the data to a new file.

  7. Repeat steps 1-6 for the Receipts file.

  8. Use a Join tool to combine the two datasets based on the Receiver's Account Number.

  9. Use a Filter tool to remove any rows where the sum of the Payments and Receipts do not match.

  10. 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
16 - Nebula

Can you share some sample rows of data?

Labels
Top Solution Authors