Conditional IF statement with AND + OR statements for SoD analysis

[jkorsten001]

Hi All,

I cannot figure out the following conditional function. 

My situation:

I want to analyse for each entity in my dataset that the USERID of NEW_ENTITYSTATUS 200 is not the same as the USERID in NEW_ENTITYSTATUS 10 OR 15.

Problem is that it might be the case that sometimes NEW_ENTIYSTATUS status 10 and Sometimes NEW_ENTIYSTATUS 15 is not available, but one of them is for sure. 

Function i tried:

IF ([ENTITY] = [ENTITY] AND [USER ID] AND [NEW_ENTITYSTATUS]="10" OR [NEW_ENTITYSTATUS]="15") = ([USER ID] AND [NEW_ENTITYSTATUS]='200')
THEN "Not desired" ELSE "Agreed"
ENDIF 

This above function is not working correctly, i think because i combined AND statements + OR statements in my formula. But i  have no idea how to write down the statement otherwise. 

I did manage to create a single function that works: 

IF ([ENTITY] = [ENTITY] AND [USER ID] AND [NEW_ENTITYSTATUS]='10' = [USER ID] AND [NEW_ENTITYSTATUS]='200') THEN ""Not desired" ELSE "Agreed"
ENDIF

In this case I am only able to check weather user ID = User ID for NEW_ENTITYSATUS 10 vs 200. 

To make clear, in my example i want to conclude that for Entity 17 the output should be Desired.

Can someone please help me out?

[TheOC]

Hi @jkorsten001 

My suggestion is likely going to involve the multi-row formula, but do you happen to be able to include the workflow so i can have a look at it directly for you?

Cheers!

[Qiu · Accepted solution]

@jkorsten001 
I tried the cross tab Tool and Formula Tool as attached.
Check if this is your desired. and please change the switch in formula tool if case sensitive.

[mbarone]

You might want to try either a cross tab or transpose tool to compare the actual values to each other by user id.  Without seeing the workflow, it's hard to tell.

But, for your formula:
IF ([ENTITY] = [ENTITY] AND [USER ID] AND [NEW_ENTITYSTATUS]="10" OR [NEW_ENTITYSTATUS]="15") = ([USER ID] AND [NEW_ENTITYSTATUS]='200')
THEN "Not desired" ELSE "Agreed"
ENDIF 

You'll want to add a condition after each AND operator.  Again, not sure what you're going for in the formula, and I like to separate out my AND/OR with parentheses.  Not always necessary, but helps me visually.  Something like:

I also prefer to use Boolean IIF because there's much less typing LOL.

Format is IIF(A,B,C)

If A, then B, else C

[jkorsten001]

This works thx all! 

[Qiu]

@jkorsten001 
Thank you for the marking.