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: Numbersearchflag1LC1REVAL2AFCACCRUAL3PROJ ACRACCRUAL4ACRACCRUAL5ACCRACCRUAL6WEWORKACCRUAL752_LACACCACCRUAL8LACACCFAMRTAMOR0920009850ACCRUAL9SCRAPRETIREMENT10SOLDRETIREMENT11WASTERETIREMENT 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

Loop Through input file and use values in formula

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

Dynamic Processing

Accepted answers

Jonathan-Sherman

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

20210322 - FindAndReplace.yxmd

Jonathan-Sherman

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

All comments

Jonathan-Sherman

Hi @henrikhollo,

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

20210322 - FindAndReplace.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280