using IF conditions within 1 dataset

[yzhang]

Hi,

I would like to use a couple of =IF(A=B,1,0) type formulas within my output data and i would like the result to be displayed in its own column, which tool is the best to use?

[ChrisTX]

Use a Formula tool with an IF function

IF c THEN t ELSE f ENDIF

IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF

Chris

[RobertOdera]

Hi, @yzhang 

I will offer that you start with the Formula Tool.

Also, based on your sample logic statement, you're looking for an IIF type argument and not IF.

However, if you're going to assess more conditions than IIF will comfortably allow and return results in the same column, then you want IF THEN ELSEIF type arguments. Good luck!

[yzhang]

if i want to see whether the item in column A is within in column B, and loop in other similar, would formula tool still be best?

[ChrisTX]

Can you provide sample input data and expected output?

If you need a loop, you'll likely need more than a Formula tool.

Chris

[yzhang]

thanks Chris, I would like to do this:

test 1 if the content of column a is contained within in column z, in new column AA, populate content with "matched", otherwise ""

test 2 if the content of column c is contained within column z, replace remaining ""  in column AA with "matched: year"

test 3 if the content of column e is contained within column z, replace remaining "" in column AA with "matched: transaction type"

i've been using the formula tool but it doesn't feel efficient enough to be doing this and i can't get past writing the formulas for the test 2 scenario so i posted this thread to see if anyone knows a better way of doing this.

thanks!

[ChrisTX]

Test data would help.

Maybe someone else can help, but I'm unable to figure out what you mean by this text...

test 2 if the content of column c is contained within column z, replace remaining ""  in column AA with "matched: year"

For the first test, in the Formula tool, make sure the data type for your new field is a String data type, then use this formula:

IF CONTAINS([column z], [column a]) THEN "matched" ELSE "" ENDIF

Example:    Contains('123ABC', 'ABC') returns TRUE

Here is a list of all functions: https://help.alteryx.com/current/Reference/Functions.htm

These links for beginners may help:

  On the left menu bar, under Academy, try Learning Paths and Interactive Lessons

Chris

[Aaron_Harter · Accepted solution]

Hi @yzhang, 

For a record by record test, you may be able to accomplish this with a multi-part conditional statement for each of the 3 tests.  If you need to compare all of the data points from fields [a], [c] & [e] then you can approach the problem with a series of Find & Replace tools, which can search for a match in any part of the cell for [z]: