Tool to apply a formula when it is NULL

[rdelmund]

Hi All, 

Just want to check what tool or script i can use that i can apply a formula on the branch column if its NULL just retain the value if its not NULL. 

 

Thanks

Rob

[RolandSchubert]

Hi Rob,

if you want to repeat the branch in subsequent rows, if they are NULL,
I would recommend to use a Multi Row Formula tool.

Formula would be like:
IF IsNull([Branch]) THEN
    [Row-1:Branch]
ELSE
    [Branch]
ENDIF

Is that what you want to do?

Best regards

Roland

[rdelmund]

Hi Roland, 

Actually i will be applying formula for those NULL row that if Counterparty starts with C  or D then Branch = AG SCF. 

Can i use the Multirow formula as well?

Thanks, 

Rob

[RolandSchubert]

I think you should you a Formula tool then.

Formula checks if [Branch] is NULL and sets the field to "AG SCF", if [Counterparty] starts with "C" or "D".

IF ISNull([Branch]) THEN
    IF Left([Counterparty], 1) = "C" THEN
        "AG SCF"
    ELSEIF Left([Counterparty], 1) = "D" THEN
        "AG SCF"
    ELSE
        [Branch]
    ENDIF

ELSE
    [Branch]

ENDIF

[rdelmund]

Thanks for sparking the Idea i just used the below formula instead and it works for me.

IF StartsWith([Counterparty], "C") THEN "AG SCF" ELSEIF StartsWith([Counterparty], "D") THEN "AG SCF" ELSE [Branch] ENDIF

no need to put the isnull condition at the start. 

[estherb47 · Accepted solution]

Hi Rob,

Since you only want to apply the formula when the Branch is null, it's important to include that in the formula, just in case other rows have counterparts that begin with C or D.

Love the use of StartsWith instead of Left!

IF IsNull([Branch]) THEN
   IF StartsWith([Counterparty], "C") THEN "AG SCF"

   ELSEIF StartsWith([Counterparty], "D") THEN "AG SCF"

   ELSE [Branch]

   ENDIF

ELSE [Branch]

ENDIF

[rdelmund]

Will take note of it. Thanks for the reminder :)