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
redray12
Level I

iterate through rows and move data to preceding row of another column

I have very limited coding experience with JSL and can do with some help to solve this problem. Basically, I have alternating rows of when tools are down and up and wanted to transfer/move the up time to a new column under the down time and compute the difference. I have attached two tables--this1 which is current data I have and this2 is the desired output.

Basically, transform from this:

this1.JPG

to this:

this2.JPG

 

 

Create new columns named: "txn_up_date" and "comment_up"

Loop through the each row and do the following:

Check if the first occurrence of a tool is "Up" under "new_availability" ;

if yes, delete the row and continue the loop

otherwise, check if the same tool has a succeeding row with a "Up" status under "new availability" column

if yes, do the following:

  • a. Transfer both the "txn_date" and "comment" of the succeeding row (i.e."Up" in new_availability column) to the preceding row's newly created "txn_up_date" and "comment_up" columns respectively.
  • b. Then keep the preceding role but delete the succeeding one which just got some of its contents transferred. if no; delete the row.
1 ACCEPTED SOLUTION

Accepted Solutions
stan_koprowski
Community Manager Community Manager

Re: iterate through rows and move data to preceding row of another column

Hi @redray12 ,

 

Here is one way to do this--

dt = Data Table("this1.jmp");

//Create some new columns
dt<<New Column("Del_Flag", Numeric);
dt<<New Column("txn_date_up", Numeric, Continuous);
:txn_date_up << Format( "y/m/d h:m:s" ); 

dt << New Column("comment_up", Character, Nominal);

//Iterate over each row looking for conditions.  When found set new column values
For Each Row(
	If( :entity[] == Lag( :entity[], 1 ) & :new_availability[] != "Down" | Row() == 1,
		:Del_Flag[] = 1,
	    :txn_date_up[Row()] = :txn_date[Row() + 1];
	    :comment_up[Row()] = :comments[Row() + 1];
	)
);

//Clean up the table by deleting rows
dt << Select Where(:Del_Flag==1 | :new_availability == "Up");
dt << Delete Rows;

//Remove temporary column
dt:Del_Flag << Set Selected;
dt << Delete Columns();

cheers,

Stan

View solution in original post

2 REPLIES 2
stan_koprowski
Community Manager Community Manager

Re: iterate through rows and move data to preceding row of another column

Hi @redray12 ,

 

Here is one way to do this--

dt = Data Table("this1.jmp");

//Create some new columns
dt<<New Column("Del_Flag", Numeric);
dt<<New Column("txn_date_up", Numeric, Continuous);
:txn_date_up << Format( "y/m/d h:m:s" ); 

dt << New Column("comment_up", Character, Nominal);

//Iterate over each row looking for conditions.  When found set new column values
For Each Row(
	If( :entity[] == Lag( :entity[], 1 ) & :new_availability[] != "Down" | Row() == 1,
		:Del_Flag[] = 1,
	    :txn_date_up[Row()] = :txn_date[Row() + 1];
	    :comment_up[Row()] = :comments[Row() + 1];
	)
);

//Clean up the table by deleting rows
dt << Select Where(:Del_Flag==1 | :new_availability == "Up");
dt << Delete Rows;

//Remove temporary column
dt:Del_Flag << Set Selected;
dt << Delete Columns();

cheers,

Stan

redray12
Level I

Re: iterate through rows and move data to preceding row of another column

@stan_koprowski 

It works perfectly. Thanks a lot.