I hope this isn’t a dumb question as I’m new around here. I’ve managed to get a long way through my first project but hit a block I can’t solve so I’m hoping somebody with more experience can advise.
I have 2 tables.
Table 1 is my primary table,
Table 2 is for lookup data.
I need to look in a column in Table 1 to see if any of the entries from a particular column in Table 2 are contained in the text string. If found, replace with a value from another column in Table 2, if not, do nothing.
So for example
Table 1 - string in “High tide time in Laguna Beach”
Table 2 - lookup column “High Tide”
Replace with “<>”
updated Table 1 string would read “<> time in Laguna Beach”
Can anyone advise a method to achieve this please? Thanks in advance.
Welcome to the Community! There are no dumb questions here, especially from newcomers
See the example below. Let me know if you have questions about the logic.
Note that in your example the lookup value “High Tide” has the 2nd word (Tide) capitalized while in the sample text string it’s not. I’m not sure if it was on purpose or not. In the example I assume that matching and replacement should be case-sensitive.
I’ve implemented this and it works really well with one exception. It now matches partial words. Yay
This has though created another issue in that the solution matches partial words. For example, from the case shared, if another lookup was “each” this would be matched with the word beach.
Is there a way to prevent this or alternatively cleanup after the fact, as I need to only match whole words.
It’s not possible out of the box. The solution would be to use all possible combinations of whitespace and punctuation, such as:
" beach "
" beach."
" beach,"
etc.
Alternatively, you can split sentences into words using the “Split delimited text into rows”, then match entire words. But that would make it hard to detect key phrases that consist of more than 1 word.
Finally, one can use the “Regular expression” action together with iterations, but that’s a very technically non-trivial approach.
in my main table I added a " " (space) to the start of the string and the end. So every word in the string started and ended with a space.
I then did the same in the lookup table
Did the match, and then removed any redundant spaces. Works great.
As you can see, only the first instance is replaced. What is the best way to handle this situation? I was thinking of adding lots of replace steps but this will be difficult for people to understand in the long term if the number of replacements grows. I have seen this kind of feature in other software whereby they have a word or synonym library.
Thanks for the response @dgudkov. I think you are probably right. I just tried iterating through the column values, exporting each individually and performing various replace actions based on a table and it as taking much longer to process than a series of replacement actions.
If you do some sort of synonym replacement, perhaps it might be an idea to have a fusion between these two current features:
