Azure Data Flow compare two string with lookup

Question

I'm using Azure Data flow to do some transformation on the data but I'm facing some challenges. I have a use case where I have two streams, these two streams have some common data, and what I'm looking for is to output the common data between these two streams. I do matching data with some common fields(product_name(string) and brand(string)), I have not got ID. to do the matching, I picked lookup activity and I tried to compare the brand in the two streams, but THE RESULT IS NOT CORRECT because for example:

left stream : the brand = Estēe Lauder

right stream. : the brand = Estée Lauder

for me this is the same brand, but they have different text format, I wanted to use 'like' operator but lookup activity does not support it, I'm using '==' operator to compare.

is there a way to override this problem please ?

Answer 1

Hi @youssef125 ,
Sorry for late reply. Here is the information I have received internally which could try for your scenario -
If you’re looking to add columns from the right side to the left side and need to use a custom expression (i.e., like) then use a Join instead with a custom Join expression.
Or, if you are looking simply to see if the value exists on the right side or not, then use either Exists or Assert. Both also support custom expressions.
Please let me know how it goes or if you have any questions.

Thanks
Saurabh