Using Not Null in Between of If Condition

[soumen]

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

[JosephSerpis · Accepted solution]

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]

@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 No	gm_ramc_PrincipalMiFIDClassification(Entity Col CN)	gm_ramc_AgentMiFIDClassification(Entity Col CP)	mtob_TOB_STATUS(PO_ext Col D)	cf_cmc_MiFIDClassification(Accounts)	Result
2	Not Null(if null check gm_ramc_AgentMiFIDClassification)	Not Null	Not Started, In Progress or Blank	Null	No Break

[danilang · Accepted solution]

Hi @soumen 

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

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?

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

The attached solution contains all three formulas

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

Dan

[JosephSerpis]

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]

Thanks Dan. 3rd one looks fine for me