Look up data from one column to another column - FIND & REPLACE help

[ewall]

Need help using the FIND & REPLACE tool (or another way to accomplish essentially a VLOOKUP from Excel). 

The value I need to "vlookup" is in column 1. The value that I need to populate, in a brand new column, is in column 2. Both data points are in the same source data in my Alteryx flow.

I currently have both the find & replace set to my source data, "Find Within Field" set to column 1, and "Find Value" set to column 2. I have "Append Fields to Record"...but I get no results. 

Clearly I'm doing some part of this wrong -- any help is greatly appreciated!!

[Emil_Kos]

Hi @ewall,

Check how the find and replace tool needs to be configured using the build-in examples.

Please write find and replace in a search bar and check build-in examples by clicking the example button:

I was doing that very often when I had issues with some tools. Actually I am still doing so but please don't say that to anyone 😀

[apathetichell]

you need a third value - ie where you are looking up your data.

column1 = what you are looking

column 2 = what you are replacing with

If you have column3 are you sure the values are there. also - is it case sensitive? If you can post some sample data someone can tell you why it's not working.

[ewall]

@Emil_Kos Yes, I use those often! Unfortunately it wasn't helpful for my issue...

[ewall]

@apathetichell I can't share the actual data. Here is a rough example:

Column 1	Column 2	Column 3	New Column
123	(null)	Closed
456	222	Closed	Closed1
789	333	Closed	Closed1
111	(null)	Closed
222	(null)	Closed1
333	(null)	Closed1

If there is a value within column 2, I want to them look up the value from column 2 IN column 1, and input the value from column 3 into a new column. So, if you see in column 1, '456' has a value of 222 in column 2. In my new column, I am populating Closed1, because when I look up 222 in column 1, the value in column 3 for 222 is Closed1. Hope that helps...

[apathetichell]

I don't see this as a find/replace situation at all. If I assume that 456 and 222 are recordids. I believe you are joining column1 on column2 and the left instance of your self-join (ie where you are using column2) is having it's column3 renamed as new column.

[ewall]

@apathetichell no, I'm not joining data sets to get column 1 and column 2. They exist together in the same source file. 456 and 222 are separate rows of data with the exact same columns. Since all of the information that I need is already there, I thought Find & Replace would help me avoid doing a join between an exact replica of the same data just to add in the column that I need, since everything is already there. If that's the only way to accomplish it, I could do that...just trying to learn more / leverage other functionality within Alteryx if it's possible.

[apathetichell]

example.

[apathetichell]

This works really easily as a self-join. You had datatype issues as well ((null) is a string null() is a value) but after corrected you may have been able to use find/replace but I don't really know. I use find/replace very infrequently since most of what it does I'd rather use a formula for. Keep in mind it's really a string compare tool - so you wouldn't want to use it for integers like your column1 was.

[ewall]

Actually, a self join isn't working because it's eliminating anything that doesn't match. I need to keep all records. Any other ideas on how to solve this? I need NEW columns added to the end of my data set that contain the values. Nulls are okay. And the numbers in my example data set aren't actually integers. They are numbers but as a string.