cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lburtone
Level I

Fill empty cells with last valid value

Hello,

 

I have the following problem. My dataset is composed by a list of rows, each representing one item produced by a machine. Normal items are not measured, but from time to time some "measurement items" are produced and the propetries measured. The assumption is that all the parts not measured will have properties similar to the latest measurment. How can I fill the empty cells using this logic?

I attached an example, so that it is easier to understand, the table looks like this:

 

Time      Order                    Test Value        (Expected)
1            Measurement            2.5                     2.5
2            Production                 -                         2.5
3            Production                 -                         2.5
4            Measurement            2                         2
5            Production                 -                         2 
6            Production                 -                         2 
7            Production                 -                         2 
8            Measurement            3                        3
9            Production                 -                         3 
10          Production                 -                         3

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ron_horne
Super User (Alumni)

Re: Fill empty cells with last valid value

perhaps the following line of script can help:

 

Names Default To Here( 1 );
dt = Current Data Table();
For( i = 2, i <= N Rows( dt ), i++,
	If( Is Missing( :test value[i] ),
		:test value[i] = :test value[i - 1]
	)
);

 

best,

ron

 

View solution in original post

4 REPLIES 4
ron_horne
Super User (Alumni)

Re: Fill empty cells with last valid value

perhaps the following line of script can help:

 

Names Default To Here( 1 );
dt = Current Data Table();
For( i = 2, i <= N Rows( dt ), i++,
	If( Is Missing( :test value[i] ),
		:test value[i] = :test value[i - 1]
	)
);

 

best,

ron

 

jerryspilTC
Level III

Re: Fill empty cells with last valid value

Hi Ron,

Can I use this for character values? Ex: my Lot ID column composed of names merged, then the last/above lot id to fill-in all blanks below it until n

 

sub << New Column( "Lot ID", Formula( :CUST Lot ID || :SPIL Lot ID || :Stage ) );
For( i = 2, i <= N Rows( sub ), i++,
	If( Is Missing( :Lot ID[i] ),
		:Lot ID[i] = :Lot ID[i - 1]
	)
);

 

Lot ID Expected (Lot ID)
 ID T41M06-21              ID T41M06-21            
   ID T41M06-21            
   ID T41M06-21            
   ID T41M06-21            
   ID T41M06-21            
   ID T41M06-21            
   ID T41M06-21            
 ID T41M06-22              ID T41M06-22            
   ID T41M06-22            
   ID T41M06-22            
   ID T41M06-22            
   ID T41M06-22            
   ID T41M06-22            
 ID T41M06-23              ID T41M06-23            
   ID T41M06-23            
   ID T41M06-23            
   ID T41M06-23            
 ID T41M06-24              ID T41M06-24            
jerryspilTC
Byron_JMP
Staff

Re: Fill empty cells with last valid value

Here are a couple of other ideas.

 

https://community.jmp.com/t5/JMP-Scripts/Fill-in-Missing-Cells/ta-p/21700

JMP Systems Engineer, Health and Life Sciences (Pharma)
Byron_JMP
Staff

Re: Fill empty cells with last valid value

This add-in has span cells feature plus a lot more.
https://community.jmp.com/t5/JMP-Add-Ins/Data-Table-Tools-Add-in/ta-p/28582
JMP Systems Engineer, Health and Life Sciences (Pharma)