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
SDF1
Super User

How to replace empty rows until next entry (and continue)?

Dear JMP community,

 

(W10, 64-bit, JMP 15.0 Pro)

 

  I have a large data table that is imported from an excel file and I need to have empty rows of a column filled with the entries of the previous list. I am trying to write JSL to go from a column as on the left to the one on the right, as shown below:

Snap1.png

  I'm thinking it should be pretty easy to code, but the issue I'm having is correctly stopping at the end of the previous product and starting with the new product. I must not be looping through correctly.

 

  Any suggestions/help is welcome.

 

Thanks!,

DS

2 ACCEPTED SOLUTIONS

Accepted Solutions
SDF1
Super User

Re: How to replace empty rows until next entry (and continue)?

Hi @Thierry_S ,

 

  Your formula probably would work, but I couldn't get it to work ideally with the particular table I have. One problem I found is that sometimes I have several rows one after the other where the Product changes, and the lag wouldn't work.

 

  I did come up with this code, which works perfectly. It's not very elegant, but it works!

 

  Thanks for your help!,

DS

 

RowReplace = dt << Get Rows Where( :Product != "" );
RowEnd = RowReplace;
Remove From( RowEnd, RowEnd[1] );
Insert Into( RowEnd, N Rows( dt ) );
		
		
For( l = 1, l <= N Items( RowReplace ), l++,
	For( irow = 2, irow <= RowEnd[l], irow++,
		If( Is Missing( :Product[irow] ) == 1,
			:Product[irow] = :Product[RowReplace[l]], 
		)
	)
);

View solution in original post

Byron_JMP
Staff

Re: How to replace empty rows until next entry (and continue)?

These are some JSL notes that will help you if you're scripting the solution 

 

 

//better version of Fill in Missing Cells, by Brady Brady

Names Default To Here( 1 );
dt = Current Data Table();

dtcollist = dt << get column names;

dlg = Dialog( "Select Column", vlist( selection = List Box( dtcollist ), button( "OK" ) ) );
Remove From( dlg, 2 );
Eval List( dlg );

dtMat = (dt << Get All Columns As Matrix);

For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = column(selection[i]);
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);

For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);
///////////////////////////// short tweak for scripting

dtcollist=dtnr<<Get Column Names;
dtMat = (dtnr << Get All Columns As Matrix);
selection={}; insert into (selection, column(1));  insert into (selection, column(2));//these columns get acted on

For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = selection[i];
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);

For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);





///////////////////////////// old version

/*
Names Default to Here(1);
dt = Current Data Table();
rows = N Row( dt );

dtcollist=dt<<get column names;

dlg = Dialog("Select Column",
vlist(
selection=listBox(dtcollist),
button("OK")));
Remove From( dlg, 2 );
Eval List( dlg );

columns=nitems(selection);
JMP Systems Engineer, Health and Life Sciences (Pharma)

View solution in original post

10 REPLIES 10
Thierry_S
Super User

Re: How to replace empty rows until next entry (and continue)?

Hi, here is a simple formula to should to the trick

If( Is Missing( :Column 1 ),
	Lag( :Column 2, 1 ),
	:Column 1
)

Best,

 

TS

Thierry R. Sornasse
SDF1
Super User

Re: How to replace empty rows until next entry (and continue)?

Hi @Thierry_S ,

 

  Your formula probably would work, but I couldn't get it to work ideally with the particular table I have. One problem I found is that sometimes I have several rows one after the other where the Product changes, and the lag wouldn't work.

 

  I did come up with this code, which works perfectly. It's not very elegant, but it works!

 

  Thanks for your help!,

DS

 

RowReplace = dt << Get Rows Where( :Product != "" );
RowEnd = RowReplace;
Remove From( RowEnd, RowEnd[1] );
Insert Into( RowEnd, N Rows( dt ) );
		
		
For( l = 1, l <= N Items( RowReplace ), l++,
	For( irow = 2, irow <= RowEnd[l], irow++,
		If( Is Missing( :Product[irow] ) == 1,
			:Product[irow] = :Product[RowReplace[l]], 
		)
	)
);

Re: How to replace empty rows until next entry (and continue)?

You have a script that will do the job, but there is an interactive solution as well. Select the column that needs the replacement so that all rows are selected. Right-click in the data grid of that column and choose Fill > Replace Missing with Previous Value.

Capture.JPG

Dan Obermiller
SDF1
Super User

Re: How to replace empty rows until next entry (and continue)?

Hi @Dan_Obermiller ,

 

  Thanks for the heads up on that. I have seen the Fill to End, to row, or fill pattern, but not that before.

 

  Do you know the JSL code that does that? The reason I ask is I want this all in JSL code because I'm reading in several files to build a large data table and need to automate the process so the end user just runs the script and out comes their table with no missing values for the product. If it's quicker and more elegant than my solution, I'd like to modify the code and insert it into my script.

 

Thanks!,

DS

Re: How to replace empty rows until next entry (and continue)?

I don't know the JSL code behind the interactive solution. That should be fine since you have multiple options there.

 

I do know that the interactive solution, like some of the scripts, can be applied to multiple columns simultaneously. Just select all of the columns and right-click, choose the Fill option.

Dan Obermiller

Re: How to replace empty rows until next entry (and continue)?

I can't find the JSL function or message that performs the same operation. I don't think it exists. There is a Main Menu() function but this command is associated with the selected column, not the main JMP menu.

Byron_JMP
Staff

Re: How to replace empty rows until next entry (and continue)?

there is a really fantastic add-in, a must-have called data table tools

there is an interpolation tool in there that does this, and so much more. Plus its much faster than the formulas on big tables.

Screen Shot 2020-05-27 at 3.10.51 PM.png

 

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

Re: How to replace empty rows until next entry (and continue)?

These are some JSL notes that will help you if you're scripting the solution 

 

 

//better version of Fill in Missing Cells, by Brady Brady

Names Default To Here( 1 );
dt = Current Data Table();

dtcollist = dt << get column names;

dlg = Dialog( "Select Column", vlist( selection = List Box( dtcollist ), button( "OK" ) ) );
Remove From( dlg, 2 );
Eval List( dlg );

dtMat = (dt << Get All Columns As Matrix);

For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = column(selection[i]);
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);

For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);
///////////////////////////// short tweak for scripting

dtcollist=dtnr<<Get Column Names;
dtMat = (dtnr << Get All Columns As Matrix);
selection={}; insert into (selection, column(1));  insert into (selection, column(2));//these columns get acted on

For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = selection[i];
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);

For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);





///////////////////////////// old version

/*
Names Default to Here(1);
dt = Current Data Table();
rows = N Row( dt );

dtcollist=dt<<get column names;

dlg = Dialog("Select Column",
vlist(
selection=listBox(dtcollist),
button("OK")));
Remove From( dlg, 2 );
Eval List( dlg );

columns=nitems(selection);
JMP Systems Engineer, Health and Life Sciences (Pharma)
SDF1
Super User

Re: How to replace empty rows until next entry (and continue)?

Hi @Byron_JMP ,

 

  Thanks for the scripting tweak code in your previous post. This one is better than my solution because it checks to see if the first entry in the column is missing, and if so, it replaces it with the next existing entry, which I have found happens in a couple imports I bring in. I am not experienced with some of the scripting solutions there, such as how you use the vec and matrix information to search up certain things. Anyway, I applied it to my specific problem, and it fixed it without a hiccup.

 

Thanks!,

DS