Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

Check if value is anywhere in another column

Highlighted
5 - Atom

Hi All,

 

I am trying to check if a Parent ID is found anywhere in an ID column. I have tried using the find and replace, find Parent ID in ID column and append to the end but it doesn't seem to be working.

 

I have attached some sample data, hopefully someone can help, ideally I would like an additional column to be Y (if it has been found) or N (it hasn't been found).

 

I believe in the sample data there should be 24 which cannot be found.

 

Any help would be appreciated.

 

thanks

 

 

Highlighted
16 - Nebula

Here's your 24 - also see workflow attached:

 

match id to parent id.png

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Here's how I would approach the problem using the find and replace tool.

 

I isolate each column as separate streams and then perform a search for the parent ID in the ID column.

 

The false stream shows your parent IDs which do not exist in the ID column.

 

Ben

 

Highlighted
5 - Atom

Hi,

 

Thanks for this, currently not on my work laptop so no access to check this all until tomorrow. Quick question however, my data has a lot more columns would this still work? If I did a join in the same way with ID and Parent ID? For example additional columns are Name, Phone Number and Address. Would this change anything?

 

thanks for your help

Highlighted
13 - Pulsar

Here is the sample workflow for your case.  Hope this is helpful. 

 

WorkflowWorkflowResultsResults

Highlighted
16 - Nebula

The key principle is to isolate the 2 things you want to compare to each other. You can then use a join or find replace as @BenMoss suggested to find matches for any of the fields you want to compare.

 

So you might end up with multiple sets of data based on the fields you picked to compare. If it's important to put it all back together to make it resemble your original dataset, you could add a record ID right at the start and then use this to join all your data streams together again at the end with a join multiple tool.

Highlighted
7 - Meteor

Thanks for this it was a tremendous help!

Labels