How can I perform a keyword search and then count the number of times each keyword is used

[wlyman]

Hello everyone,

I need to conduct a key word search in the item descriptions of about 10,000 orders. Item descriptions are not standardized, so they can be of any length. Here is some sample data:

Order #	Order Amount	Item Description
123	$100	Blue and green jacket
456	$150	A red hat
789	$175	Short socks
987	$25	Black shoes

Keywords:

1. Blue

2. Hat

3. Short

In addition to finding the keywords, I need to be able to display a count for each keyword. 

I tried using the find & replace function along with a RegEx formula, but could not get what I needed. Any help is greatly appreciated!

[AngelosPachis]

Hi @wlyman ,

You can make use of the Regex_countmatches function which is embedded within the formula tool

That formula will count how many times a word appears in a string and it is case insensitive. You could turn it into case sensitive by adding a 0 at the end of the function, i.e.

REGEX_CountMatches([Item Description], "Blue",0)

Hope that helps, let me know if that worked for you.

Regards,

Angelos

[pedrodrfaria]

Hi @wlyman 

Please see attached the workflow below:

Pedro.

[Tyro_abc]

Hi @wlyman 

I created a small app for this, let me know if you need any explanation.

Best Regards

Arundhuti

[wlyman]

Thank you for the reply! I like your answer, however, I need to be able to view the order total by keyword as well. I am not quite sure how to do that.

[wlyman]

Thank you for the reply! I like your answer, however, I need to be able to view the order total by keyword as well. I am not quite sure how to do that.

[AngelosPachis]

@wlyman  you can probably do that with a summarize tool, summing the values per record id

Cheers,

Angelos