8 Replies Latest reply on Jun 11, 2018 11:30 PM by ambrose chen

# Fill the null with previous value

I got a dataset there were thousands of Pro_id ,here  I just listed 2 kinds of Prod_id.

Could someone help me to create a new column "Fill_Null", it fill the null values of column "Points" with the previous value.

Thanks a lot

Ambrose

 Date Prod_id Points Fill_Null 2017/6/23 A001 2017/6/30 A001 2017/7/1 A001 2500 2500 2017/8/2 A001 2500 2017/9/1 A001 2000 2000 2017/9/8 A001 2000 2018/2/1 A001 500 500 2018/2/2 A001 500 2018/2/13 A001 500 2017/8/1 J093 2017/9/1 J093 5000 5000 2017/9/15 J093 5000 2017/9/22 J093 5000 2017/10/1 J093 1000 1000 2017/10/6 J093 1000 2017/12/1 J093 2500 2500 2017/12/8 J093 2500 2017/12/15 J093 2500 2018/3/1 J093 1000 1000 2018/3/2 J093 1000 2018/3/9 J093 1000
• ###### 1. Re: Fill the null with previous value

Hello ambrose,

Here is a simple example using Table Calculation Functions

As you can found from the screenshots, the disadvantage is that, table calculation only picks JUST the data from previous row.

If you want to fill city a's value to city c, you have create more calculated fields.

Regards

Lei

• ###### 2. Re: Fill the null with previous value

Hello ambrose and Lei,

Actually by one calculated field.

and need to set table calc,

Thanks,

Shin

• ###### 3. Re: Fill the null with previous value

Feel shocked by the fact that I haven't noticed previous_value() before \\//

• ###### 4. Re: Fill the null with previous value

That formula's logic is a bit different from other table calc.

Cannot be used backwards, can be used like circular calc etc.

Shin

• ###### 5. Re: Fill the null with previous value

Hi Lei,

i found this is quiet useful:

if isnull(LOOKUP( ATTR( [Points] ),0 )) then PREVIOUS_VALUE( ATTR( [Points] ) )

else ATTR( [Points] ) end

But it can NOT fill by Prod_id!!!!!

• ###### 6. Re: Fill the null with previous value

1 of 1 people found this helpful
• ###### 7. Re: Fill the null with previous value

Thanks a lot !

It's a miracle function

• ###### 8. Re: Fill the null with previous value

hi Shinichiro Murakami,

GREAT JOB!!!