Hello there, I need some help on updating SQL table, where there is 0 values in the row update it with the last non null values from the rows. Loan IDDateCredit Limit12008-01-31 00:00:00.00020012008-02-29 00:00:00.000012008-03-31 00:00:00.00030012008-04-30 00:00:00.00030012008-05-31 00:00:00.000012008-06-30 00:00:00.00040012008-07-31 00:00:00.0000 Expected results: Loan IDDateCredit Limit12008-01-31 00:00:00.00020012008-02-29 00:00:00.00020012008-03-31 00:00:00.00030012008-04-30 00:00:00.00030012008-05-31 00:00:00.00030012008-06-30 00:00:00.00040012008-07-31 00:00:00.000400 I have tried multi row formula like below, but it doesn't work: IF ISNull([CreditLimit]) then [Row-1:CreditLimit] ELSE [CreditLimit] ENDIF Can someone please help? Thank you for your time!

Replace last null values with last non null/blank values

Hello there,

I need some help on updating SQL table, where there is 0 values in the row update it with the last non null values from the rows.

Loan ID	Date	Credit Limit
1	2008-01-31 00:00:00.000	200
1	2008-02-29 00:00:00.000	0
1	2008-03-31 00:00:00.000	300
1	2008-04-30 00:00:00.000	300
1	2008-05-31 00:00:00.000	0
1	2008-06-30 00:00:00.000	400
1	2008-07-31 00:00:00.000	0

Expected results:

Loan ID	Date	Credit Limit
1	2008-01-31 00:00:00.000	200
1	2008-02-29 00:00:00.000	200
1	2008-03-31 00:00:00.000	300
1	2008-04-30 00:00:00.000	300
1	2008-05-31 00:00:00.000	300
1	2008-06-30 00:00:00.000	400
1	2008-07-31 00:00:00.000	400

I have tried multi row formula like below, but it doesn't work:

IF ISNull([CreditLimit]) then [Row-1:CreditLimit]

ELSE [CreditLimit]

ENDIF

Can someone please help?

Thank you for your time!

Accepted answers

clmc9601

Hi @tk12,

Your formula will have to be a little different if you're putting it in a new field. The previous formula will work if you update the existing field. If you create [New Field], the formula should be as follows:

IF ISNull([CreditLimit]) or [CreditLimit] = 0

THEN [Row-1:New Field]

ELSE [CreditLimit]

ENDIF

All comments

clmc9601

Hi @tk12,

Yes, multi-row formula is the way to solve this! Instead of doing IsNull, I'd adjust your formula as follows:

IF ISNull([CreditLimit]) or [CreditLimit] = 0

THEN [Row-1:CreditLimit]

ELSE [CreditLimit]

ENDIF

Hope this helps!

tk12

Thank you for the @clmc9601 response. But unfortunately it doesn't work. I somehow feel I might need to change the datatype somewhere for some column. Not sure.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703