How do I find an exact value in a string then append a value to the table

[JaySmith123]

Hi, I have the following problem that I just can’t seem to solve but it seems so simple to do in Alteryx or Excel. Any help will be much appreciated.

I have a list of URLs

|#| URL
|1| abuja/wuse/property
|2| abuja/wuse-2/property

And a list of Lookup Values with another column called return values

|#| Search Term| Return Term
|1| abuja/wuse| Wuse|
|2| abuja/wuse-2| Wuse 2|

I would like to find the EXACT search term in the list of URLs and then append the Return Term in a new column so I end up with a result like this

#| URL | Return Term
|1| abuja/wuse/property| Wuse
|2| abuja/wuse-2/property| Wuse 2

When I use the LIKE expression it returns the #2 as Wuse and not Wuse 2

I hope this makes sense?

Thanks

[aworker]

Hi @JaySmith123 and welcome to the KNIME forum !

@JaySmith123

When I use the LIKE expression it returns the #2 as Wuse and not Wuse 2

Would it be possible please to have a snapshot of your -Rule Engine- node (I guess is this node you are using) or at least copy and paste here the text of the rules you have written ?

May be there is a problem with the conditions you are using in your rules.

Thanks & regards,

Ael

[JaySmith123]

Hi @aworker

Yes this is the expression I use in the rule engine;

$URL$ LIKE “abuja/wuse” => “Wuse”
$URL$ LIKE “abuja/wuse-2” => “Wuse 2”

But both return Wuse.

Thanks

[gonhaddock]

Hi @JaySmith123
Try to invert the order of your rules.
The first one needs to by applied to ‘Wise 2’, as the other one results ambiguous for both cases.

BR

[JaySmith123]

Hi @gonhaddock thanks for your suggestion.

It seemed to work on a small dataset so thank you but I have 1000s and when I tried your same method it didnt work exactly as I would have liked. But thank you anyways for your help I’ll try to find some other way lol

[JaySmith123]

Hi @aworker @gonhaddock

Actually is there a way to extract the value from the string, append it to a new column and then simply do a sort of VLOOKUP to bring in the return value?

Something like this;
String - Search Term - Return Term
wwww.npc/abuja/wuse/property - abuja/wuse - Wuse
wwww.npc/abuja/wuse-2/property - abuja/wuse-2 - Wuse 2

Hope this makes sense.

[aworker]

Hi @JaySmith123

@JaySmith123

$URL$ LIKE “abuja/wuse” => “Wuse”
$URL$ LIKE “abuja/wuse-2” => “Wuse 2”

@gonhaddock is right suggesting that the two conditions need to be inversed for the simple reason that if you have a $URL$ where the second condition is TRUE, the first will definitely be TRUE too but will never be checked.

The way the conditions works is a kind of if CONDITION1 then RESULT1 else if CONDITION2 then RESULT2 else … etc.

Just a comment about posting sentences with wild cards or other special characters that may be interpreted as web meta-characters. Here in your example they are not visible but they exist. It is preferable to insert code in posts with the option </> “Preformated Text” which preserves the content of the code inserted in the post. The right conditions should look then like this when they are posted:

$URL$ LIKE “*abuja/wuse-2*” => “Wuse 2”
$URL$ LIKE “*abuja/wuse*” => “Wuse”

with the wild cards “*” visible

Just a last comment, it is also always good to add an extra last condition which is executed if none of the previous were TRUE, for instance:

$URL$ LIKE “*abuja/wuse-2*” => “Wuse 2”
$URL$ LIKE “*abuja/wuse*” => “Wuse”
TRUE => "Nothing was changed or whatever you need as result here in this case"

This prevents to generate empty values as result if none of the previous condition was TRUE.

Hope this helps

Best

Ael

[JaySmith123]

Hi @aworker it worked! Thank you I really appreciate your help and I’ve noted the coding preference for when I next post a question on Knime.

Thank you and @gonhaddock

[aworker]

@JaySmith123 glad it worked !

Thanks & best wishes

Ael