Alternative to Contains function

[peterlyngvig]

Hi everyone,

I am trying to make some code that would help me categorize various credit card transactions. The credit card transaction data is in the attached input.csv - now my goal is to add a new column, which shows which store the credit card has been used in - see output.csv. As the data in the "Item" column comes in all kinds of formats (e.g. sometimes store name is in the beginning of the string, sometimes in the end, sometimes in the middle), I am currently using the "Contains" function and a bunch of IF statements to search for the desired words in the "Item" column. That works, but as I am now looking for a growing list of +25 different store names, I have a pretty long set of nested IF statements (see attached code).

s there a smarter way to do this? Any input would be highly appreciated.

[DataNath · Accepted solution]

@peterlyngvig for this scenario, I'd probably use the Find & Replace tool. Although the lookup table may take a little while to set up, it'll be significantly quicker than typing out a massive if statement as per your current approach, and easy to add to in future.

You basically set up your lookup table like so, where [String Check] here is the target that will be looked for within [item], and [Company Name] is the company name you want to be appended if that match is found.

Your Find & Replace tool configuration should then look like this. Find within field is your target field (item) that the tool will be looking within. As the target substring can be anywhere as you mentioned in your post, we need to select 'any part of field' and if the name can vary in terms of capitalisation, opt for 'case insensitive find'. Under the 'find value' dropdown, this is where we first reference our reference table - here the Find & Replace tool essentially becomes a Contains() function where it's basically saying, Contains([item], [String Check]). Upon a successful match, we then want to display the relevant [Store Name] next to that record, which is why we set the final part of the configuration to do just that:

Your outcome will then look like this:

The reason there's a lot of nulls is because I only pulled a few targets/company names from your if statement to build a quick lookup table example so matches haven't been found for all records.

If you'd like any further explanation or have any extra questions then fire away!

[peterlyngvig]

Thanks @DataNath, this was really helpful :)