Question - Find & Replace tool

reginawhelan

I am creating a data validation flow and can't seem to get the Find & Replace to do what I expect. 

Normally, in Excel I would use a Vlookup formula.

Data Set:

1st -  Used Text to Columns to separate two values into 2 columns.

2nd - Cleansed data to remove any Leading or Trailing Whitespace.

3rd - Changed the Data Type to V_String. 

Lookup Table:

1st - Cleansed data to remove any Leading or Trailing Whitespace.

2nd - Created a new column. 

Find & Replace Function:

Find: Entire Field

Find Within Field: Selected the column from the Data Set 

Find Value: Selected column from Lookup Table

Match Whole Word Only

Append Field(s) to Record: New Column 

The results in the New Column are Null. I would like for them to be the lookup value from the Table. If the Data Set Value is not in the Lookup table I would expect the new column to have a Null value.

echuong1

The JOIN tool achieves the same functionality as a vlookup in excel. 

The J output would be the succesful vlookup (rows with values). The L and R outputs would be the nulls. To simulate a traditional vlookup, you can use the join tool and then union the J output and L/R output (whichever side your full data population is connected to).

See attached for an example - first purchase date is the field brought in using the 'vlookup'.

Let me know if that works!

New Workflow4.yxmd