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

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

redray12 — Wed, 25 Sep 2019 17:56:28 GMT

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:

to this:

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.

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

stan_koprowski — Thu, 26 Sep 2019 01:09:37 GMT

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

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

redray12 — Thu, 26 Sep 2019 02:19:40 GMT

@stan_koprowski

It works perfectly. Thanks a lot.