Previous non-NULL value

[BartVA]

I have a table with multiple consecutive rows with NULL values, after and in between rows with non-NULL values.
I tried to fill in using:

If(IsNull(balance_end_real) and journal_id = Previous(journal_id) and company_id = Previous(company_id), Previous(balance_end_real), balance_end_real) as balance_end_real

to fetch the previous value (balance_end_real) from the previous row with the same journal_id and company_id.

This works fine if there are no consecutive NULL values. For consecutive NULL values however, the first fill in is correct (as it finds a non-NULL value above), but the second fill-in is NULL (as the previous row was NULL).

[pravinboniface]

How about something like this to replace the Nulls with the max value available for a given journal_id and company_id?

Left Join

Load journal_id,
         company_id
         max(balance_end_real) as balance_end_real_new
Resident Table;

Drop Field balance_end_real;
Rename balance_end_real_new to balance_end_real;

[Oleg_Troyansky · Accepted solution]

I'd try to replace the Previous() function in the assignment part of the formula with the function Peek(). The main difference is that the Previous() function evaluates your expression from the previous row in the input table, and these NULL values will remain NULL there, while Peek() works with the previously loaded value in the target table, where the NULL value should get replaced by the previous non-null value.

So the new formula should look like this:

If(IsNull(balance_end_real) and journal_id = Previous(journal_id) and company_id = Previous(company_id), Peek('balance_end_real'), balance_end_real) as balance_end_real

Notice that Pick() requires single quotes around the field name. Also the proper sorting of the data in this load is important.

You can read more about these two functions in this blog article:

Q-Tip #12 – Peek() or Previous() ? 

Cheers,

[BartVA]

Yes, that seemed to do it, thanks!