This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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'.
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.