All Apps and Add-ons

Find a value from a lookup table inside a field from search

genesiusj
Builder

Hello,

I have a lookup table that contains some words: ANGEL, DEVIL, CHURCH, KING, LOVE etc.
I have a search that returns a list of garbled letters: GJKLSER, WIUPAF, NVSDEVILDFP, QNJSANGELW, KINGGVSCHURCH, TRANGELOVEMGX, etc.

Need to find when the word from the lookup is contained in the list of garbled letters (highlighted red above). Also need to know which word(s) were found, as in the green example above; including if the lookup words overlap, as in the purple letter example above.

Each word in the lookup table has a corresponding score, which needs to be included in the results. 

Lastly, the lookup table contains over 1000 words/scores. Otherwise, I would think a foreach would work.

Thanks in advance for ideas, thoughts, direction.

God bless and safe and healthy to you and yours,
Genesius

 

Labels (1)
Tags (3)
0 Karma
1 Solution

bowesmana
Champion

In your lookup, add the words with leading and trailing * characters and make a lookup definition that sets the match type for that field as wildcard, e.g.

WILDCARD(fieldName) 

then when you lookup your field, it will get a match. Use lookup like this

| lookup lookup_definition word OUTPUT word as found_word score

so, the word actually found will be returned as a new field found_word

Hope this helps

 

View solution in original post

bowesmana
Champion

In your lookup, add the words with leading and trailing * characters and make a lookup definition that sets the match type for that field as wildcard, e.g.

WILDCARD(fieldName) 

then when you lookup your field, it will get a match. Use lookup like this

| lookup lookup_definition word OUTPUT word as found_word score

so, the word actually found will be returned as a new field found_word

Hope this helps

 

View solution in original post

genesiusj
Builder

@bowesmana 

That was PERFECT!

While I have made lookup defs before, I never used them. I didn't know that the def could be used in place of the file name in the lookup command. I understood about the WILDCARD and my lookup included *ANGEL*, etc.

Thanks and God bless,
Genesius

0 Karma