For loop question

[#1]

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 

[#2]

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

[#3]

@MikeD_Anderson I think it is using the nested loops on purpose. The break() in the inner loop should help find the first future row to copy a value from and then stop testing more future rows. 

@Yotam you might need to make the break() for another case as well: a second if statement for entity[i] != entity[j]. This is not the solution to the current problem, but if your table gets very large, the inner loop should not have to run to the end of the table for the last entity in each entity group. The run time could go from N^2 to linear.

@Melanie_J_Drake Good use of the date column. Possibly the original columns were character, and strings like "9JAN2019" and "10JAN2019" would not compare the same as their date values. 

[#4]

@Craige_Hales,
Oooh! I missed that he was looking for multiple entries in the same field... Thanks for setting me straight!

M

[#5]

I created a data table like what you described and ran your script against, and I did get some values inserted into the value2 column. Is it possible that your table just doesn't have any values that meet your conditions?

Here is what I did:

1. Created a table with these five columns and 100 rows.
2. Column 1 is entity, and I filled it with Random Integers between 1-100. (so some values are repeated, some values don't appear.)
3. Column 2 is date1. I made it y/m/d and filled with sequence data (incrementing every day) from October 1, 2019 to January 8, 2020.
4. Column 3 is date2. I made it y/m/d and filled it with Random Integers that matched the date range in date1. (so some days are repeated, some days don't appear.)
5. Column 4 is value1. I filled with with Random Uniform between 0 and 1.
6. Column 5 is value2, left empty.
7. I sorted date1 descending, and then I sorted entity ascending.
8. Then I ran your script, and it did populate some rows in value2 with values from value1.

Here is the script for the table I created:

New Table( "Untitled",
	Add Rows( 100 ),
	New Column( "entity",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[1, 1, 2, 5, 7, 8, 10, 11, 12, 12, 13, 14, 14, 15, 16, 17, 17, 18, 20,
			21, 21, 23, 25, 25, 26, 26, 30, 30, 31, 32, 33, 35, 37, 38, 38, 39, 40,
			41, 42, 45, 48, 48, 48, 49, 49, 49, 49, 49, 50, 50, 54, 54, 55, 55, 55,
			56, 56, 57, 57, 61, 64, 65, 66, 66, 67, 67, 68, 71, 72, 73, 73, 75, 76,
			77, 77, 79, 79, 79, 80, 81, 83, 84, 85, 85, 85, 86, 87, 88, 90, 92, 92,
			93, 94, 95, 95, 96, 97, 98, 98, 98]
		)
	),
	New Column( "date1",
		Numeric,
		"Continuous",
		Format( "y/m/d", 12 ),
		Input Format( "y/m/d" ),
		Set Values(
			[3660595200, 3653596800, 3655324800, 3657916800, 3657571200, 3659904000,
			3655670400, 3658867200, 3660768000, 3655843200, 3655497600, 3661286400,
			3657139200, 3659817600, 3657744000, 3655411200, 3653078400, 3656448000,
			3653856000, 3659299200, 3654374400, 3653251200, 3656707200, 3654115200,
			3659212800, 3655756800, 3654979200, 3654288000, 3654201600, 3660249600,
			3658176000, 3657225600, 3653510400, 3658608000, 3654892800, 3658780800,
			3654806400, 3656188800, 3659040000, 3659990400, 3657657600, 3656880000,
			3652992000, 3659644800, 3658089600, 3655238400, 3654028800, 3653164800,
			3658348800, 3652992000, 3659385600, 3656102400, 3659558400, 3657312000,
			3655929600, 3653424000, 3653337600, 3657484800, 3656016000, 3661113600,
			3653164800, 3660854400, 3660076800, 3655152000, 3661027200, 3658435200,
			3656620800, 3654547200, 3656534400, 3657830400, 3657052800, 3654633600,
			3660163200, 3658953600, 3653078400, 3660422400, 3658262400, 3655065600,
			3658694400, 3658521600, 3655584000, 3659731200, 3659472000, 3659126400,
			3653769600, 3654460800, 3660681600, 3660940800, 3660508800, 3653942400,
			3653683200, 3656361600, 3658003200, 3660336000, 3656966400, 3656793600,
			3654720000, 3661200000, 3657398400, 3656275200]
		)
	),
	New Column( "date2",
		Numeric,
		"Continuous",
		Format( "y/m/d", 12 ),
		Input Format( "y/m/d" ),
		Set Values(
			[3654288000, 3654547200, 3660940800, 3655238400, 3655756800, 3653078400,
			3655152000, 3661113600, 3655670400, 3658521600, 3657916800, 3660854400,
			3660336000, 3655497600, 3654288000, 3658867200, 3655929600, 3658780800,
			3654460800, 3654288000, 3659472000, 3654374400, 3655584000, 3658435200,
			3661286400, 3655843200, 3657484800, 3657225600, 3654460800, 3659040000,
			3659904000, 3657744000, 3656016000, 3661113600, 3660854400, 3658435200,
			3656275200, 3658953600, 3660508800, 3660940800, 3654892800, 3654374400,
			3656966400, 3655065600, 3656620800, 3656448000, 3653424000, 3657312000,
			3653596800, 3653078400, 3659126400, 3658089600, 3661027200, 3655929600,
			3660249600, 3656102400, 3659040000, 3655411200, 3656275200, 3657916800,
			3654979200, 3658176000, 3657830400, 3657830400, 3658867200, 3659644800,
			3655670400, 3658262400, 3658867200, 3655584000, 3653942400, 3658435200,
			3656448000, 3657571200, 3654201600, 3656620800, 3658262400, 3656620800,
			3658262400, 3660595200, 3661200000, 3660595200, 3654028800, 3653078400,
			3659990400, 3659817600, 3659558400, 3659817600, 3654201600, 3656534400,
			3656707200, 3660508800, 3656102400, 3658003200, 3654979200, 3653683200,
			3659126400, 3654115200, 3660595200, 3657830400]
		)
	),
	New Column( "value1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.26654487522319, 0.268102226778865, 0.0855446415953338,
			0.573256081901491, 0.356754610314965, 0.754618955776095,
			0.707293681567535, 0.423604927491397, 0.0621724124066532,
			0.678517441963777, 0.591030938085169, 0.155546535504982,
			0.265113049885258, 0.216979010729119, 0.410901310387999,
			0.14603908569552, 0.672683654818684, 0.615730489837006,
			0.283979720901698, 0.569958938285708, 0.395094690844417,
			0.740726810414344, 0.69660765840672, 0.592704318463802,
			0.0819160102400928, 0.951408985769376, 0.622255727648735,
			0.666835095966235, 0.735137741314247, 0.302926567615941,
			0.135769379558042, 0.909960085060447, 0.159030285663903,
			0.721727871336043, 0.084829821716994, 0.133812531363219,
			0.775578187312931, 0.721618666080758, 0.824164194054901,
			0.791532889008522, 0.596487301168963, 0.432518238201737,
			0.803037486504763, 0.963547020917758, 0.483634022762999,
			0.256266842130572, 0.184637202415615, 0.368785659084097,
			0.433026649756357, 0.322859298205003, 0.63024612586014,
			0.366389756789431, 0.31168167036958, 0.215982228284702,
			0.506428832421079, 0.914496833458543, 0.00391270662657917,
			0.911203439114615, 0.158799458295107, 0.0707558316644281,
			0.0671632892917842, 0.626141374465078, 0.481671805493534,
			0.175614738604054, 0.345019530970603, 0.998095875838771,
			0.88658848637715, 0.352987453807145, 0.427561661927029,
			0.664279501885176, 0.0914310747757554, 0.8409428098239,
			0.483058925019577, 0.812691684346646, 0.184209692059085,
			0.956565208500251, 0.133405604865402, 0.358119377400726,
			0.416565118124708, 0.289288628380746, 0.51707451720722,
			0.294909303309396, 0.108170761261135, 0.084945646347478,
			0.978223626734689, 0.369202323025092, 0.00625932472757995,
			0.590033906744793, 0.253290069289505, 0.313401067862287, 0.4109431670513,
			0.178761875722557, 0.781808004016057, 0.686178727308288,
			0.0606778394430876, 0.432180172763765, 0.761295787990093,
			0.578681491082534, 0.197850840399042, 0.98485347116366]
		)
	),
	New Column( "value2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
	)
);

If this isn't like your table, what is your table like?

HTH,

Melanie

[#6]

Thanks to everyone for responding!

@MikeD_Anderson Maybe I misunderstood you, but I think I do need the nested loop, because for each row I'm testing the values from different rows as well, so I think I do need those indexes (i,j).

@Melanie_J_Drake After you gave your example table and said it was working, I tried to figure out what is going wrong with my data. My table did have values that met the conditions, so I was pretty puzzled. Turns out that before the loop I added the new column for value2 (just with missing values) incorrectly.

Here is what I did at first:

dt<<New Column("last_lot_thickness",

Numeric,

Continuous,

Format( "Best", 12 ),

Formula(.));

It took me some time to understand that this was the reason I was not getting values into value2 during the loop. Only when I did the following I got my result:

dt<<New Column("value2",

Numeric,

Continuous,

Format( "Best", 12 ));

I actually still don't understand what difference does it make, but turns out it solved the problem. Do you have any idea why?

@Craige_Hales I'm not sure I completely understood what you said. My table is about 30,000 rows and it actually does take some time for the script to run. Could you be more specific where you would put the extra break?

Thanks to all! 

[#7]

the speed up; it even helps with Melanie's small example:

dt = Current Data Table();
start=tickseconds();
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();
		);
		if(dt:entity[i]!=dt:entity[j],break());// speedup
	)
);
show(tickseconds()-start)

since the entity values are sorted, no need to study all the following rows where they can't match.

The answer for the question you directed to Melanie: putting formula(.) in the column specification makes the column a formula column. A formula column has no data of its own; it uses the formula expression (a missing value in your example) to compute the value. A more typical formula would use other columns, but constants are OK too. It does seem like you should get a log message when assigning to a formula column. @EvanMcCorkle 

[#8]

I got it! You are absolutely right, it saves a lot of time. Thanks!