Categorizing bank transactions based on lookup table with strings

Hi all,

I want to ask you for a favor. I am struggling for weeks with the following problem:

I have a csv with banktransactions, amounts and descriptions. I am thinking of a way to categorize these banktransactions based on specific string value in the descriptions (contains..). So basically I have two files:

File A: Banktransactions:

Date/Amount/Accountno/Description

for example: 2021-01-31/31.00/5368798564/Loan payment HSBC Bank reference 1326464

File B: Lookup

Search-String/Category

for example: Loan Payment/Mortgage

Eventually I would like to have all of File A and an extra column with the category field of File B.

What is the best method to do this in Alteryx?

Thanks!

Common Use Cases

Accepted answers

Qiu

@YiuW86
so you are saying the original record is one column.

Sure a macro does the work, but also Append will do it.

New Workflow3A.yxmd

All comments

Qiu

@YiuW86
If I get you correctly, we can use Join or Find and Replace tool.
as I showed in the sample, for join, the case of the string must also same.
For Find and Replace, it can do case insensitve.

New Workflow3.yxmd

YiuW86

Thanks. But I would like to have this result:

2021-01-31/31.00/5368798564/Loan payment HSBC Bank reference 1326464/Mortgage

I dont want any of the original file/record replaced. Basically what I want to achieve is to have first the list of transactions and behind that to append a column with the category field where the search string is contained within the bank description.

YiuW86

Maybe a macro is more fitting. Alteryx checks for each record in the strings and categories whether it can be matched for the first record of banktransactions, if not, it continues with the next record in the strings and categories file. If all doesnt match it adds "not found". If found it does the same for the second record of banktransactions etc

Qiu

@YiuW86
so you are saying the original record is one column.

Sure a macro does the work, but also Append will do it.

New Workflow3A.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703