For loop question

Yotam

Hi everyone,

First time I'm writing here, though I have read in this forum a lot.

I do have some experience in JSL scripting, but I still haven't dealt with for loops.

I have this data table which has 5 columns: entity, date1, date2, value1, value2 (value2 column initially contains missing values only).

The table is sorted by Entity (ascending) and date1 (descending).

I want to loop through all rows, and insert values from value1 column into value2 column, based on the following condition:

IF entity[row i] == entity[row j] AND date1[row i] > date2[row j] THEN insert the value from value1[row j] into value2[row i]

Once a value is inserted, just continue to the next row.

So far I have this:

 

dt = Current Data Table();

For( i = 1, i <= N Rows( dt ), i++, 
	For( j = i, j <= N Rows( dt ), j++, 
		If(
			And(
				Column( dt, "entity" )[i] == Column( dt, "entity" )[j],
				Column( dt, "date1" )[i] > Column( dt, "date2" )[j]
			), 
			Column( dt, "value2" )[i] = Column( dt, "value1" )[j];
			Break();
		)
	)
);

 

But I'm not getting any values inserted to value2.

I would love to get some help on this. Hope I explained myself well.

Thanks 

MikeD_Anderson

Hi, @Yotam !

 

I'd start by using the For Each Row() construct in the data table.  It's a lot easier to use when you're looping throgh a data table.  Also, I think you only need one loop, since your indexing on row not through two lists with different lengths.  Some of my favorite resources on the topic are below:

 

https://community.jmp.com/t5/JSL-Cookbook/Loops/ta-p/50949

https://www.jmp.com/support/help/14-2/iterate-on-rows-in-a-table.shtml

 

 

Best,

 

M

 

Update:

 

I think this code will do what you want: 

 

// Name the data table
dt = Current Data Table();

//For each row compare the dates and populate value 2 with value 1 if they are equal.
For Each Row( dt, If( :date1 == :date2, :value2 = :value1 ) );

Also, the first line of your code is missing the glue (;) which may be causing you a problem.  

 

M