topic Re: Check if values in one column exist in another and update a third. in Alteryx Designer Desktop Discussions

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

DMDingo — Mon, 03 Dec 2018 19:09:59 GMT

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!

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

Thableaus — Mon, 03 Dec 2018 18:47:46 GMT

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,

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

jdunkerley79 — Mon, 03 Dec 2018 18:59:20 GMT

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

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

DMDingo — Mon, 03 Dec 2018 19:10:27 GMT

I have added a little example table in the question.

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

DMDingo — Mon, 03 Dec 2018 19:11:02 GMT

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

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

jdunkerley79 — Mon, 03 Dec 2018 19:34:42 GMT

Yep works fine - have updated the sample

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

DMDingo — Mon, 03 Dec 2018 20:32:56 GMT

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?

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

jdunkerley79 — Mon, 03 Dec 2018 20:44:32 GMT

Ah I had assumed unique column A and B.

A minor adjustment - summarise B to unique values should fix