Create a new column for rows that have specified key words

Question

Hello everyone

As the title states I am trying to create a new column that "Flags" key words found in a dataset. My datasets are broken out as follows:

-     "Properties" File: A "Description" column contains property descriptions for thousands of properties

-     "Key Words" File: A "Key Words" column contains a list of about 12 key words

For example, if one of the key words I want to flag is "New Build", I would want to create a new column in my dataset that just says "New Build" for properties that have this key word in the description column. I am using a "CONTAINS" function, but have had to manually type in each keyword. Any idea on how I could expedite this so the CONTAINS function just pulls key words from my "Key Words" file?

Any help would be appreciated!

SeanAdams · Answer

Hey @rkarsan - as @gabrielvilella says you can use the Find & Replace tool.    There's also another way to do this which is a bit more complex by transposing the data into single rows (one row per keyword) and then doing a join.

With the find & replace - there's a few ways:

- Configure it to add a new column (screenshot below)

- Configure it to replace any of the matching keywords with a character you can look for (e.g. ~) and then you can just do a contains for that

So - few options for you to play with - post back once you have something that works for you so that we can all learn from you?

Sean

gabrielvilella · Answer

Try using the Find and Replace tool. You can add a new column with the flag value and then append this column.

If the flag is the same for every record you can even have a formula with that "New" value so you don't have to manually type it in.

Flag.yxmd