Alteryx Designer

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

Multiple if and then statments

Highlighted
8 - Asteroid

Hi, I am struggling with an multiple IF statement. I have tried many ways and keep getting either Malformed If Statement. I am using the statement below:

 

IF Contains([Taxonomy used],'E')
AND if ([Matching Status]='MATCHED')
THEN "RECONCILED" ELSE Null()
ENDIF

 

I also wanted to add a couple of other statements to the formula and haven't attempted this yet as struggling with adding just one IF statement above.

 

IF Contains([Taxonomy used],'E')
AND if ([Matching Status]='UNMATCHED1')
THEN "UNMATCHED" ELSE Null()
ENDIF

 

IF Contains([Taxonomy used],'E')
AND if ([Matching Status]='UNMATCHED2')
THEN "MATCHED" ELSE Null()
ENDIF

 

Any suggestions?

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
IF Contains([Taxonomy used],'E',0)
AND ([Matching Status]='UNMATCHED1')
THEN "UNMATCHED" ELSE Null()
ENDIF

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
13 - Pulsar

Try:

 

IF Contains([Taxonomy used],'E') AND [Matching Status]='MATCHED' THEN 'RECONCILED'
ELSEIF Contains([Taxonomy used],'E') AND [Matching Status]='UNMATCHED1' THEN 'UNMATCHED'
ELSEIF Contains([Taxonomy used],'E') AND [Matching Status]='UNMATCHED2' THEN 'MATCHED'
ELSE Null() ENDIF

 

image.png

 

This should get you what you need.

Highlighted
ACE Emeritus
ACE Emeritus

To elaborate a little more on @MarqueeCrew's answer, the problem with your initial formulas is the second IF.

 

The IF statement can test multiple things at the same time, so you don't need to have an IF for each of them.

 

IF Contains([taxonomy used],'E') AND [Matching Status] ='MATCHED' THEN 



That will resolve this syntax error.

Highlighted
8 - Asteroid

Thanks all for your help. It works :)

Highlighted
8 - Asteroid

Hi All,

I need some more help please it has come a bit more complicated unfortunately. The above formula worked however I have missed something. There are 2 sides to my trades ( Ours/Trade vs Clients/Allege) and the client side does not have the Taxonomy populated but I need the Taxonomy and match status on both the Trade/Allege sides. So now I need to create 2 new columns "New Unmatched status" & "Taxonomy updated".

 

New column: Taxonomy Updated - Where Trade ID is the same for both Trade and Allege (column A) if Taxonomy Used is E on the Trade side Taxonomy updated needs to have E for both sides. If Taxonomy used is blank on Trade side then remain blank for both sides so that I can use the formula above as currently only getting one side of the trade with the correct new matched status. I have added a table below.

 

Sorry for the example table format, I cannot paste a table or picture as it is blocked internally. Any help would be appreciated :)

 

 

Trade/Allege          Trade ID                Matching Status           Taxonomy Used         Current formula only updates one side     Taxonomy Updated        New Unmatched status

Trade                     ABC123                 MATCHED                   E                                 RECONCILED                                               E                                RECONCILED 

Allege                    ABC123                 MATCHED                                                       MATCHED                                                    E                                 RECONCILED 

Trade                     DEFC123              UNMATCHED1            E                                 UNMATCHED                                                E                                UNMATCHED     

Allege                    DEF123                 UNMATCHED1                                                UNMATCHED1                                             E                                UNMATCHED     

Trade                     GHI123                 UNMATCHED2            E                                  MATCHED                                                    E                                 MATCHED   

Allege                    GHI123                 UNMATCHED2                                                UNMATCHED2                                             E                                 MATCHED   

Trade                    JKL123                  RECONCILED                                                  RECONCILED

Allege                   JKL123                  RECONCILED                                                  RECONCILED

Trade                    MNO123                UNMATCHED                                                  UNMATCHED

Allege                   MNO123                UNMATCHED                                                  UNMATCHED

 

 

Highlighted
ACE Emeritus
ACE Emeritus

Based on the layout you have below, the Multi-Row Formula tool is going to be your best bet.


If I understand your example properly, here's an example formula to create Taxonomy Updated

 

IF [Trade/Allege] = 'Trade' Then [Taxonomy Used] ELSEIF [Trade/Allege] = 'Allege' then [Row-1:Taxonomy Updated]
ELSE NULL()
ENDIF

 

You could also Group By the Trade ID field to make sure that you won't pull in unexpected values.

 

Does that help with what you need?

Highlighted
8 - Asteroid

Oh WOW thank you so much, that worked a treat :)

 

Labels