Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Nested If Then Else Statement - Multiple Conditions - Incorrect Data Returned

rgalik
7 - Meteor

Hello, 

 

I am working on a dataset that needs to check three topics and return a result. The topics have an AND and OR operator to them. However it seems that Alteryx only returns half of the question. IE  if There is a Tax Director and a Tax Partner it will read 'OK' but it will not Flag the 'Topic's Columns that do not have those partners on them. 

 

if Contains([Questionnaire], 'MONEY') AND [Director] >= 1 OR [Employee] >=1 THEN 'OK'

ELSEIF Contains([Questionnaire], 'MONEY') AND [Director] = 0 OR [EMPLOYEEl] = 0 THEN 'FLAG'

ELSE '' ENDIF

5 REPLIES 5
Claje
14 - Magnetar

So it looks like what you are saying in the first case is:

Return 'OK' where one of the following is true:
Questionnaire contains 'MONEY' AND Director >= 1

 

OR

 

Employee >= 1

 

So any case with Employee >= 1 will return even if the Questionnaire does not contain Money.

 

Is this correct?

If not, try adding parentheses around the "Director OR Employee" logic, as below:

( [Director] >= 1 OR [Employee] >=1 )

The ELSEIF probably has the same issue.


If this isn't the problem you are trying to resolve, let me know and I'll take another look!

rgalik
7 - Meteor

Hi there, 

 

I tried the logic and it didn't seem to work. What I am trying to do is something like the following:

 

DirectorEmployeeQuestionnaireFLAG
1 MONEYOK
34  
  MONEYFLAG
12MONEYOK
 1  
 3  
11  

 

So if there is a questionnaire, and either it has a director or an employee involved, it's OK. If there is a questionnaire with nobody involved, it needs to be flagged.

Claje
14 - Magnetar

I used the following formula, which worked for two of the three FLAGs:

if Contains([Questionnaire], 'MONEY') AND ([Director] >= 1 OR [Employee] >=1) THEN 'OK'

ELSEIF Contains([Questionnaire], 'MONEY') AND ([Director] = 0 OR [EMPLOYEE] = 0) THEN 'FLAG'

ELSE '' ENDIF

It did not work for the case that should say "FLAG", returning nothing instead.  the reason for this was that Director and Employee have NULL values, not empty values.

 

You could tweak your formula to account for NULL and 0 values, but instead of that, I would recommend adding a Data Cleansing tool before your formula, and configuring it to replace NULLs with 0's in the Director and Employee fields.

rgalik
7 - Meteor

Thanks for your speedy reply. 

 

I did use the data cleansing tool, to replace the nulls with Zero's however it doesn't seem to be working. 

 

It just stays 'Null'

 

 

Claje
14 - Magnetar

Your data type might be a String instead of a number.

You could change the data type, but here's a tweaked formula that might work: 

 

if Contains([Questionnaire], 'MONEY') AND ([Director] >= 1 OR [Employee] >=1) THEN 'OK'

ELSEIF Contains([Questionnaire], 'MONEY') THEN 'FLAG'

ELSE '' ENDIF

In theory, I think you want any case where the record is not OK and the Questionnaire CONTAINS 'Money' to be flagged.  This formula removes the other comparison and simply flags any 'MONEY' record that is not 'OK'.

Labels