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

Using Not Null in Between of If Condition

soumen
6 - Meteoroid

Hi All,

I am trying to use a not null in between of If statement in Formula.

I have 2 Columns with hold almost similar attributes and I want to have a function which will make sure any one of the 2 columns have some value while completing my condition.

In below formula Underlined are my 2 Cols & values are in (). 

 

if IsNull([cf_cmc_MiFIDClassification]) AND
[mtob_TOB_STATUS] IN ("Not Started","In Progress") AND [gm_ramc_PrincipalMiFIDClassification] IN ("Professional","ECP","Non-MiFID","Retail","Not-Applicable") AND
[gm_ramc_AgentMiFIDClassification] IN ("Professional","ECP","Non-MiFID","Retail","Not-Applicable")
then "No Break" else "Break"
endif

Could you please help?

Thanks

Soumen

5 REPLIES 5
JosephSerpis
17 - Castor
17 - Castor

Hi @soumen 

                      This syntax will test if these two columns have a value. You may want to to use IsEmpty if there are empty value and this formula also test for IsNull or IsEmpty at the same time you would just need to be ! to test for not. 

 

if IsNull([cf_cmc_MiFIDClassification]) AND
[mtob_TOB_STATUS] IN ("Not Started","In Progress") AND !IsNull([gm_ramc_PrincipalMiFIDClassification]) AND
!IsNull([gm_ramc_AgentMiFIDClassification])
then "No Break" else "Break"
endif

soumen
6 - Meteoroid

@Joseph

Thanks for the help, Its working I guess.

If my understanding is correct, "!IsNull" syntax removes the Empty values and only consider the non empty values available? Below was my Rule

 

Rule Nogm_ramc_PrincipalMiFIDClassification(Entity Col CN)gm_ramc_AgentMiFIDClassification(Entity Col CP)mtob_TOB_STATUS(PO_ext Col D)cf_cmc_MiFIDClassification(Accounts)Result 
2Not Null(if null check gm_ramc_AgentMiFIDClassification)Not NullNot Started, In Progress or BlankNullNo Break
danilang
19 - Altair
19 - Altair

Hi @soumen 

 

Here's the result of using your existing formula on various combinations of values that test all the conditions

 

r.png

 

If both of the gm_rmac_ columns are null then result = "Break".  What should the result be?

 

Note: the current way that the formula is written the result can only be "No Break" if the first column is NULL.  Should the formula start with  

 

if NOT IsNull([cf_cmc_MiFIDClassification]) AND...

 

Which results in this?

r2.png

 

If you want to provide a result if either of the gm_ramc_ columns is valid you can group them together using brackets and an OR between them like this

 

if NOT IsNull([cf_cmc_MiFIDClassification]) AND
[mtob_TOB_STATUS] IN ("Not Started","In Progress") AND

([gm_ramc_PrincipalMiFIDClassification] IN ("Professional","ECP","Non-MiFID","Retail","Not-Applicable") Or
[gm_ramc_AgentMiFIDClassification] IN ("Professional","ECP","Non-MiFID","Retail","Not-Applicable"))
then "No Break"

else "Break"
endif

 

resulting in this with no break if either column is valid

r3.png

The attached solution contains all three formulas

 

If none of these are correct, please provide some sample inputs with the corresponding results

 

 

Dan

 

JosephSerpis
17 - Castor
17 - Castor

Hi @soumen !IsNull(Column) while find all the rows that are not Null !IsEmpty(Column) which is a different formula will do checks 1.Not empty and 2. Not Null.

soumen
6 - Meteoroid

Thanks Dan. 3rd one looks fine for me

Labels