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

[rgalik]

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

[Claje]

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]

Hi there, 

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

Director	Employee	Questionnaire	FLAG
1		MONEY	OK
3	4
		MONEY	FLAG
1	2	MONEY	OK
	1
	3
1	1

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]

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]

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 · Accepted solution]

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'.