Check if values in one column exist in another and update a third.

[DMDingo]

I am new to Alteryx and cannot find a solution for this. We have three columns of data:

Column A is the ID
Column B is Parent's ID (like hierarchy)

Column C is the Type

For each row, we need to lookup if Column A exist anywhere in Column B, and overwrite Column C with a new value if this is True. If not, no change.

Here is an example:

Row	A	B	C
1	215		Associate
2	122	215	Associate
3	554		Manager

For this example, because "215" is showing in B, we would want the Value C in Row 1 to update to "Manager". All other values in C would need to stay the same.

Thank you!

[Thableaus]

Hi @DMDingo

Could you give us an example of how your data looks like?

It's possible, but I wanna know one thing:

For ex., if Parent ID is "112", and ID is "12", you have "12" present on Column B....then you want to overwrite column C with which value?

Is that the situation? 

Cheers,

[jdunkerley79 · Accepted solution]

Based on what you described:

 

1. Self join the data to itself (A to B)

2. L output is stuff not preset, J output is stuff present

3. Use a formula tool to manipulate column C

4. Use a union tool to put it back together

Sample attached but if you can post some fake data easier to get closer

[DMDingo]

I have added a little example table in the question.

[DMDingo]

Thanks for the info. I have added an example table in the question. Would this still work?

[jdunkerley79]

Yep works fine - have updated the sample

[DMDingo]

EDIT - For some reason it was duplicating the rows. Added Unique before the Formula and fixed it.

Thanks, this was close but did cause some issues with the output.Can you run this for this data set? In this case we would see 123 in B and update the top to to "Parent".

A	B	C
123		Unknown
123		Unknown
154	123	Child
554		Parent
554		Parent

What we have are multiple rows that share the same group, and the group needs to be updated across them. When I ran this on the data set, I went from 89,000 rows to 1.7 million. 

So, updated your workflow and it still works. What would be causing our number to sky rocket?

[jdunkerley79]

Ah I had assumed unique column A and B.

A minor adjustment - summarise B to unique values should fix