Loop Through input file and use values in formula

[henrikhollo]

Hi There,

I have an excel file input in my workflow which has a column called Description. I would need to create a new column and categorize (flag) each row based on what the description field contains.

What would need to be looked for within the description comes from another input, like:

Number	search	flag
1	LC1	REVAL
2	AFC	ACCRUAL
3	PROJ ACR	ACCRUAL
4	ACR	ACCRUAL
5	ACCR	ACCRUAL
6	WEWORK	ACCRUAL
7	52_LACACC	ACCRUAL
8	LACACCFAMRTAMOR0920009850	ACCRUAL
9	SCRAP	RETIREMENT
10	SOLD	RETIREMENT
11	WASTE	RETIREMENT

So I would need Alteryx to iterate through this file, first search for 'LC1' in the description field, if it contains it then in the new column mark it as 'REVAL', otherwise check for 'AFC' and so on until it either it finds a flag or until all keywords were searched for.

I'm kinda new to Alteryx, this would be an easy loop in VBA, could someone please suggest a way of doing this in Alteryx?

Many thanks in advance,

Henrik

[Jonathan-Sherman · Accepted solution]

Hi @henrikhollo,

I'd use a Find and Replace tool for this, appending the flag from the lookup table to the original data source. If it doesn't need to be case sensitive you can also tick "Case Insensitive Find" to turn that off.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Kind regards,

Jonathan

[henrikhollo]

Hi Jonathan,

Many thanks for your quick reply. It seems much easier than I thought it'd be 🙂

One last question on the topic if I may: I see there are three options in the tool (beginning, any part and entire field) for finding the keyword, however what if i'd like to search for the given word in the first 3 characters or in the last 6 of the description? is that also doable with this tool?

Many thanks,

Henrik

[Jonathan-Sherman · Accepted solution]

HI @henrikhollo,

Great question! You wouldn't be able to search in the first or last N words directly within the tool, however you could use a formula tool with the LEFT() and RIGHT() functions to create a field for the first three characters and another field for the last six characters and use the same Find and Replace tool technique for those fields against the lookup table?

Kind regards,

Jonathan

[henrikhollo]

True, thanks a lot for your quick and detailed response.

Have a great day!

Kind regards,

Henrik

[Jonathan-Sherman]

No problem at all, and you!