Converting this Excel Formula into Alteryx

Hello,

I'm having trouble converting the following Excel formula into an Alteryx Formula.

I could do this very simply if it was more than one tool, but I want to summarize the below into just as single Formula node.

I've made an attempt but can't seem to fulfill the below.

Field 1, 2 and 3 would usually be referencing a speciifc cell in Excel, but I've formatted it to reference the specific field.

IF([Field 1] ="Hello",
IF(AND([Field2]="John",[Field3]=20),"TRUE",
IF(AND([Field2]="Sam",[Field3]=30),"TRUE","FALSE"
)),"Not Available")

Essentially, "FALSE" will only occur when [Field 1] is "Hello", but does not fulfill any of the other conditions.

Whereas, Not Available will occur when [Field 1] is anything but "Hello".

And "TRUE" will only occur when "Hello" is fulfilled and any of the "John" + "20" or "Sam" + "30".

Future wise, in the workflow I consider TRUE and Not Available the same, so an alternative solution would be just to identify records where [Field 1] = "Hello" and is not everything which is John + 20 OR Sam + 30.

Thanks for anybody's help.

Tips and Tricks

Expression

Accepted answers

grossal

Hi @R_L,

You could also consider using Switch Case for this, this is definitely way more readable and less complicated.

I'll copy the expression for you to copy and test it:

Switch([helper], "Not Available",
"HouseSam20","True",
"HouseThomas30","True",
"HouseGerald40","True",
"HouseMike45","True",
"HouseShannon50","True",
"HouseLinda55","True")

A simple helper builds the concatenation of the three columns and the switch case helps with the rest.

Regards

Alex

All comments

MarqueeCrew

Let's try:

[Field 1] ="Hello" AND

([Field2]="John" AND [Field3]=20) OR

([Field2]="Sam" AND [Field3]=30)

THEN "TRUE"

ELSEIF

[Field 1] ="Hello" THEN

"FALSE"

ELSE
"Not Available"

ENDIF

CHEERS,

mark

R_L

Hi, no luck unfortunately as it seems the

ELSEIF

[Field 1] ="Hello" THEN

"FALSE"

Seems to overwrite the:

[Field 1] ="Hello" AND

([Field2]="John" AND [Field3]=20) OR

([Field2]="Sam" AND [Field3]=30) THEN "TRUE".

In Excel; the formula would look like this:

=IF(A2="HOUSE",

IF(AND(B2="SAM",C2=20),"TRUE",
IF(AND(B2="THOMAS",C2=30),"TRUE",
IF(AND(B2="GERALD",C2=40),"TRUE",
IF(AND(B2="MIKE",C2=45),"TRUE",
IF(AND(B2="SHANNON",50),"TRUE",
IF(AND(B2="LINDA",C2=55),"TRUE","FALSE"))))))),"NOT AVAILABLE")

Generally, if it fulfills the House Condition for A2, it should present the "FALSE" statement but ONLY IF it doesn't fulfill the "Sam" AND "20" etc.

Let's assume A2 falls under the field [Type], B2 under [Name] and C2 under [Age].

I've written the below but it seems seems to involve the FALSE condition overwriting everything else:

IF [Type] = "HOUSE" AND [Name] = "SAM" AND [Age] = 20 THEN “TRUE”
ELSEIF [Type] = " HOUSE " AND [Name] = "THOMAS" AND [Age] = 30 THEN “TRUE”
ELSEIF [Type] = " HOUSE " AND [Name] = "GERALD" AND [Age] = 40 THEN “TRUE”
ELSEIF [Type] = " HOUSE " AND [Name] = "MIKE" and [Age] = 45 then “TRUE”
ELSEIF [Type] = " HOUSE " AND [Name] = "SHANNON" AND [Age] 50 then “TRUE”
ELSEIF [Type] = " HOUSE " AND [Name] = "LINDA" AND [Age] = 55 then “TRUE”

ELSEIF [Type] = "HOUSE" then “FALSE”

ELSE “Not Available”

ENDIF

grossal

Hi @R_L,

You could also consider using Switch Case for this, this is definitely way more readable and less complicated.

I'll copy the expression for you to copy and test it:

Switch([helper], "Not Available",
"HouseSam20","True",
"HouseThomas30","True",
"HouseGerald40","True",
"HouseMike45","True",
"HouseShannon50","True",
"HouseLinda55","True")

A simple helper builds the concatenation of the three columns and the switch case helps with the rest.

Regards

Alex

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703