Converting this Excel Formula into Alteryx

[R_L]

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.

[MarqueeCrew]

Let's try:

IF

[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

[RolandSchubert]

Hi @R_L ,

I think you should use nested conditions - try this formula:

IF [Type] = 'House' THEN
   IF [Name] = 'Sam' AND [Age] = 20 THEN
      'True'
   ELSEIF [Name] = 'Thomas' AND [Age] = 30 THEN
      'True'
   ELSEIF [Name] = 'Gerald' AND [Age] = 40 THEN
      'True'
   ELSEIF [Name] = 'Mike' AND [Age] = 45 THEN
      'True'
   ELSEIF [Name] = 'Shannon' AND [Age] = 50 THEN
      'True'
   ELSEIF [Name] = 'Linda' AND [Age] = 55 THEN
      'True'
   ELSE
      'False'
   ENDIF
ELSE
   'Not Available'
ENDIF

Best,

Roland

[grossal · Accepted solution]

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