Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
redray12
Level I

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

@stan_koprowski 

It works perfectly. Thanks a lot.

Article Labels

    There are no labels assigned to this post.