If AND function

[leelak]

Hi, As I am new to this platform I am facing difficult to write expression for a field in Alteryx. 

Could you please help me out. The below formula we are using in Excel.

=IF(AND(OR(AC2="TS Financial Advisory",AC2="TS Integration & Separation"),OR(X2="TS Financial Advisory",X2="TS Integration & Separation"),((ISNUMBER(SEARCH("TS",E2)))=TRUE),((ISNUMBER(SEARCH("@",Y2)))=TRUE),((ISNUMBER(SEARCH("@",Z2)))=TRUE),((ISNUMBER(SEARCH("SPA",C2)))<>TRUE),((ISNUMBER(SEARCH("Secondment",C2)))<>TRUE),((ISNUMBER(SEARCH("TVP",C2)))<>TRUE),((ISNUMBER(SEARCH("Sandars hannah",O2)))<>TRUE),AND(J2<>"002631;",J2<>"007834;",J2<>"007855;",J2<>"007808;",J2<>"007809;",J2<>"007885;",J2<>"007886;",J2<>"007812;",J2<>"007813;",J2<>"007887;",J2<>"007888;"),L2>DATEVALUE("7/27/2018")),"IN Scope","Out of Scope")

And please provide the link to learn on how to write expressions in Alteryx

[mbarone]

Hi @leelak ,

See conditional functions in the help files for information on IF statements.  Within that page, there'll be a link regarding operators , which you'll also want to review.  All these are within the functions section of the help files, which will definitely be worth your time to review.

Glance those help files over, and give it a try!  If you don't have any luck, let us know what you've tried and we'll try to help.

Cheers!

[leelak]

 Hi mbarone,

Thanks for the response. Could you please make the changes to above formula provided, to make it fit for Alteryx inorder to arrive output ?

Thanks for the links provided to get a knowledge on conditional expressions.

[mbarone]

@leelak 

That would defeat the purpose of you learning how to do it and being able to apply it for future workflows.  As community leaders, the Alteryx ACEs help users expand their skills and knowledge of how to use Alteryx.  We don't simply "give the answer".  I again strongly suggest you review the links and try it out yourself.  As I said, if you try it out and can't anywhere, provide the steps you've tried and we can help.

[carlosteixeira]

Hi @leelak 

A hint. In addition to the ones the @mbarone  already gave.

Start solving IFs and ORs from the inside out.

Create separate conditions and then join them one by one and testing this will make your work easier.

These steps will help you to understand and minimize the possible trials and errors you may have to make.

If you had any questions after trying to do it, we will be here to help

Good luck.

[grossal · Accepted solution]

Hi @leelak,

@RolandSchubert and I just had a little fun playing a round with it and want to provide you with a little guide what's needed to get to your result.

Let's first break down your Script into various parts:

The base structure of this looks rather easy in Alteryx:

IF 
(Condition)
THEN 'IN Scope'
ELSE 'Out of Scope'
ENDIF

But what about the condition?

In Excel you separate all 'AND' conditions with a comma, in Alteryx you add another 'AND' and it would look like this:

(Condition 1) AND (Condition 2) AND (Condition 3) AND ...

Therefore we are already at the following:

IF 

OR(AC2="TS Financial Advisory",AC2="TS Integration & Separation") AND
OR(X2="TS Financial Advisory",X2="TS Integration & Separation") AND
((ISNUMBER(SEARCH("TS",E2)))=TRUE) AND
((ISNUMBER(SEARCH("@",Y2)))=TRUE) AND
((ISNUMBER(SEARCH("@",Z2)))=TRUE) AND
((ISNUMBER(SEARCH("SPA",C2)))<>TRUE) AND
((ISNUMBER(SEARCH("Secondment",C2)))<>TRUE) AND
((ISNUMBER(SEARCH("TVP",C2)))<>TRUE) AND
((ISNUMBER(SEARCH("Sandars hannah",O2)))<>TRUE) AND
AND(J2<>"002631;",J2<>"007834;",J2<>"007855;",J2<>"007808;",J2<>"007809;",J2<>"007885;",J2<>"007886;",J2<>"007812;",J2<>"007813;",J2<>"007887;",J2<>"007888;") AND
L2>DATEVALUE("7/27/2018")

THEN 'IN Scope'
ELSE 'Out of Scope'
ENDIF

Time to resolve them one at a time. The first 2 OR-Statements compare a column (AC) to a list, we can do this in Alteryx like this:

[Column] IN ('list value 1','list value 2')

[AC] IN ('TS Financial Advisory','TS Integration & Separation')

This can be applied for the second condition with the X-column as well.

The next one in your list is a check if a text-snippet is part of a longer text. This can be done in Alteryx using the 'contains' method:

((ISNUMBER(SEARCH("TS",E2)))=TRUE)

Contains([Column],'text to look for')

Contains([E],'TS')

We can use exactly the same with a ! in front to reflect your <> for 'SPA', TVP', etc.

Would you like to take a guess what we need for J? Right. Another list comparison like we did it with AC! 

Time for the last one, What about L2? We don't need something like datevalue, we simply need to format the date in the right way (yyyy-MM-dd) and the comparison works for itself.

L2>DATEVALUE("7/27/2018")

[L] > '2018-07-27'

That's it. As a learning journey, you should now definitely try to put everything together yourself and not only copy what you'll see in the spoiler tag below here. It's fine if you take it, use it for now and than come back later to understand it once you have the time, but make sure you understand what's going on for future cases.

Spoiler

IF 
(
   [AC] IN ('TS Financial Advisory','TS Integration & Separation') AND
   [X] IN ('TS Financial Advisory','TS Integration & Separation') AND
   Contains([E],'TS') AND 
   Contains([Y],'@') AND 
   Contains([Z],'@') AND 
   !Contains([C],'SPA') AND 
   !Contains([C],'Secondment') AND 
   !Contains([C],'TVP') AND 
   !Contains([O],'Sandars hannah') AND 
   [J] NOT IN ('002631','007834','007855','007808','007809','007885','007886','007812','007813','007887','007888') AND
   [L] > '2018-07-27'
)
THEN 
   'IN Scope'
ELSE 
   'Out of Scope'
ENDIF

Let us know what you think.

Best

Roland & Alex

[leelak]

Hi,

Itried in the below way. Please correct me where went wrong. 

if [Service Line_For Tracker Update]="TS Financial Advisory" OR [Service Line_For Tracker Update]="TS Integration & Separation" AND IF [Service Line]="TS Financial Advisory" OR [Service Line]="TS Integration & Separation" AND IF Contains("TS",[Profit Center Text] AND IF Contains("@", [EL Email]) AND IF Contains("@", [EM Email]) AND IF Contains("SPA", [Name of Order]) OR Contains("Secondment", [Name of Order]) OR Contains("TVP", [Name of Order]) OR Contains("Sandars hannah",[Name of Eng. Leader]) AND [Service Code]!="002631" AND [Service Code]!="007834" AND [Service Code]!="007855" AND [Service Code]!="007808" AND [Service Code]!="007809" AND [Service Code]!="007885" AND [Service Code]!="007886" AND [Service Code]!="007812" AND [Service Code]!="007813" AND [Service Code]!="007887" AND [Service Code]!="007888" THEN "IN Scope"
Else "Out Scope" ENDIF

I am getting Parse error (858) Malform IF function. I checked my data types as well. All are into V_Wstring.

[carlosteixeira]

Hi @leelak!

Try to use this syntax.

IF
(
([Service Line_For Tracker Update] IN ("TS Financial Advisory","TS Integration & Separation")) AND
([Service Line] IN ("TS Financial Advisory","TS Integration & Separation"))AND
(Contains([Profit Center Text],"TS") AND Contains([EL Email],"@") AND Contains([EM Email]"@") AND Contains([Name of Order],"SPA" )) OR
(Contains([Name of Order],"Secondment") OR Contains([Name of Order],"TVP")) OR
(Contains([Name of Eng. Leader],"Sandars hannah")) AND
([Service Code] NOT IN ("002631","007834","007855","007808","007809","007885","007886","007812","007813","007887","007888"))
)
THEN "IN Scope"
Else "Out Scope" ENDIF

Thanks