Help with contains and replace a value.

sreddy01

Hi Alteryx Community,

I need help to find and replace a value in a given column.

Sheet1: 

Sheet2:

I want to pick a row from "PLACE" column in Sheet 1 and lookup in "VALUE" column in Sheet 2. If an exact match is found (in any given order), then replace that with "NAME" column from Sheet 1 and leave the extra records as is. I tried doing this with find and replace tool. But there are couple of things that I observed. If there is a mismatch in the order, it is leaving those records. The required output is in Column "Expected Output". And the total count of replaced records here is 8, whereas I am getting 4. Out of which the highlighted 4th row is also an error since it is replacing only those in same order and leaving the others.

Thank you very much for any recommendation.

TestBook1.xlsx

apathetichell

Row 4 is not an error per se - you just need to clarify a hierarchical logic. IE in the statement:

IF contains ("plant,flower,butterfly") then 1 elseif contains ("plant,flower,butterfly,toyota") then 2, else 3 endif

statement 2 logically WILL NEVER BE TRUE. That is because if it was true, statement 1 would be true - and then the Then clause attached to statement 1 would execute. - does that clear it up?

To fix this - start with the most restrictive and work your way to the least restrictive...  having said that - I'd probably run a record id, split to rows by comma and then do some kind of match. Then I'd see which components matched and run from there... you'd want to reconfigure your tests into most restrictive - and make sure the components are in alphabetical order.