Categorizing bank transactions based on lookup table with strings

[YiuW86]

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!

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

[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 · Accepted solution]

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

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

[YiuW86]

Qiu, thank you very much. That works. Much appreciated. have a nice day!