If Value for Period is Null, Replace with Value from Other Period

[hellyars]

I want to say...

if [PERIOD]="B" && ISNULL([RIGHT_AMNT]) then

Get Value for the intersection of [PERIOD]="BY1" && [RIGHT_AMNT]

else [RIGHT_AMNT]

endif 

PERIOD	AMNT	RIGHT_AMNT		DESIRED RIGHT_AMNT
P	200	100		200
C	100	150		100
B	175	Null		153
O	25	Null		Null
BY1	200	153		153
BY2	300	50		50

[mceleavey]

Hi @hellyars ,

use a formula tool on the column you wish to amend. 

if isnull([RIGHT_AMNT]) then [AMNT] else {RIGHT_AMNT] endif

or, if the cell is actually populated with the word Null, then:

if [RIGHT_AMNT]="Null"  then [AMNT] else {RIGHT_AMNT] endif

Hope this helps.

M.

[hellyars]

Since PERIOD is a pattern that repeats for each set of records, I tried using a Multi-Row Tool.

if [PERIOD]="B" and isnull([Right_AMNT]) then [Row+2: Right_AMNT] else [Right_AMNT] endif 

But, it does not seem to work.  It replaces the Null with a 0 instead of the correct value.

[hellyars]

@mceleavey My bad.  I should not have used the same values for B.  The values from AMNT and Right_AMNT will not always be the same.  Will correct, thanks.

[hellyars · Accepted solution]

It did not work with the real data at first, but now it is -- so that's a win.  

if [PERIOD]="B" and isnull([Right_AMOUNT]) then [Row+2:Right_AMOUNT] else [Right_AMOUNT] endif