Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

Question - Find & Replace tool

Meteoroid

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.

 

 

 

 

 

 

Highlighted
Alteryx
Alteryx

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!

Highlighted
Meteoroid

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. 

 

Highlighted
Alteryx
Alteryx

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.

Highlighted
Meteoroid

Genius! Thank you so much!

Labels