Alteryx designer Discussions

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

How to compare one column to another and return a value to a new column in a data source?

Highlighted
6 - Meteoroid

Hi there,

 

Happy Thursday! 

 

My question is as follows, it would be great if any one of you experts could provide some insights for solving the problem.

 

The following is the original data 

 

TesterPreparerReviewer1Reviewer2Reviewer3
FTFTSRRJFY
SRFTSRRJ 
FYFTSRRJ 
RJFTSRRJFY

 

 

This following is the result that I want to get

 

TesterPreparerReviewer1Reviewer2Reviewer3Result
FTFTSRRJFY1
SRFTSRRJ 1
FYFTSRRJ 0
RJFTSRRJFY1

 

I want to compare column one's data to column 2, 3, 4, and 5 and then shows the result in a new column (column 6) with the following values 

1. Return value 0 if the data in column 1 does not match to any of the columns from column 2-5

2. Return value 1 if the data in column 1 does match to one of the columns from column 2-5

 

Any of your inputs will be extremely helpful! Thank you in advance.

 

Best,

 

M

Highlighted
8 - Asteroid

The Multi-Row Formula Tool should work

 

multi-row.PNG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@rarmstrong's solution work but no need to use multi-row if you are only referencing the active row. Standard formula tool will work with the exact formula in the post.

 

Ben

Highlighted
ACE Emeritus
ACE Emeritus

Multi-field formula is not required in @rarmstrong's example -- the regular Formula tool should work.

 

The Contains syntax is fine for your example data, but I'd be a bit careful if the examples are just for illustration -- a Contains query looks anywhere in the string, so Tester "Al" would match with Reviewers Alan, Alfred, and Alice.

 

I'd be tempted to modify the formula to:

 

If [Tester]=[Preparer]

or [Tester]=[Reviewer1]

or [Tester]=[Reviewer2]

or [Tester]=[Reviewer3]

Then 1 Else 0 Endif

 

If you needed something more dynamic, where the list of fields was changing or was fairly extensive, I think you could work with Transposing the data using a Group By on your [Tester] field and then using some Formulas.

Highlighted
6 - Meteoroid

It worked! Thank you!

Labels