Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live!


Question - Find & Replace tool

8 - Asteroid

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.








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!

8 - Asteroid

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. 



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.

8 - Asteroid

Genius! Thank you so much!