Replace last null values with last non null/blank values

[tk12]

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! 

[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.

[clmc9601]

Hi @tk12,

Can you please send a screenshot of your multi-row formula's configuration window? And the incoming and outgoing data, if you can? 

[tk12]

@clmc9601 

Here is the screenshot for formula: 

Here is the workflow: 

Here is the output: 

 Please let me know if you need more details. Thank you! 

[clmc9601]

Hi @tk12,

Try unchecking the group by [AsOfDate]. That means that you're treating every date separately, and to my understanding, that's not your intention. Similarly, you'll want to set rows that don't exist to Null or 0. As you have it currently, you're taking the next value, not the previous value.

Changing these two configuration options should fix it! 

[tk12]

Hello @clmc9601 

Thank you. That worked, but now I have one more problem. Please see below screenshot, New Field should have 2000 from 2009-02-28 but it's not updating. Thank you! 

[apathetichell]

Change values of rows that don't exist  to are null() - right now you are imputing them.

Also since you are grouping by LoanID make sure there is a value in that specific loanid.

[clmc9601 · Accepted solution]

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

[tk12]

@clmc9601  @apathetichell  

IT worked. You guys are awesome. Thank you very much for your time. Have a wonderful weekend!