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 · Accepted solution]

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!

[reginawhelan]

Thank you. I tried the Join tool and some of the vales in the data set were not in the reference table, therefore, were not brought back in the output. I also need to know when a value in my data set is not within the reference table - it can't be dropped. 

[echuong1 · Accepted solution]

The records not in the reference table will be isolated within the L/R output (depending on which side the full population is connected to). To bring these values back in, a UNION tool is used. 

See the example attached to my previous post.

[reginawhelan]

Genius! Thank you so much!

[jjrussell3]

One of the Alteryx lessons recommends using the F&R tool to simulate the Vlookup - I couldn't get it to work so I'm going to try your method.