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 PERIODAMNTRIGHT_AMNT DESIRED RIGHT_AMNTP200100 200C100150 100B175Null 153O25Null NullBY1200153 153BY230050 50

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

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

Expression

Accepted answers

hellyars

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

All comments

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.

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703