topic Re: Fill rows in main table depending on conditions in second table in Discussions

Fill rows in main table depending on conditions in second table

SymS — Sat, 10 Jun 2023 23:43:18 GMT

I have a main table with timestamp for 2021 in one minute resolution ("Timestamp-1min"). Need to fill the main table with data from second table with one-day resolution ("Start-End-TS1") depending on a pair of conditions for each day in a year(Sunrise, Sunset: Twilight Start and End) while matching the day, month and [time(Hr:min) in the conditions].

I have done it for Date 6/14/2021- which is in the "Timestamp-1min-Result" table.

I added one column -"S Start-S End"- which is Sunrise-Start, Sunrise-End in the same column at the matching times for every day and another column for Twilight(Start and End) likewise. I also want to populate the Day Length, Mid T, Twilight time and a code column (DT/TT)- Daytime/ Twilight time which will help in performing statistics with another table that I would join later. How can I just use formulas to populate the Total day time and Twilight time (In the results table) instead of taking values from the second table. How can I create the code for Daytime alone (as DT) and Twilight time (as TT) alone?

How to do it interactively? OR Is JSL the only way?

Re: Fill rows in main table depending on conditions in second table

txnelson — Wed, 26 Jan 2022 09:38:59 GMT

The only efficient way to do what you want is to use a piece of JSL. Doing it using multiple formula columns would force the reading through a half a million records multiple times. The JSL below reads through the main table only once.

Also, you could do this all in one table, but then you would have to pretend you are using Excel and move all of the data from the lookup data table into the main table and then hid the columns. It wouldn't be pretty. Here is the script that I think should get you a long way into solving your issue. With a half a million records it takes a few minutes to do all of the required comparisons. Please note, the script is looking for 2 data tables with specific names.

Names Default To Here( 1 );
dtMain = Data Table( "Timestamp-1min" );
dtLookUP = Data Table( "Start-End-TS1" );

// Add the required new colummns
dtMain << New Column( "S Start-S End", format("h:m:s"));
dtMain << New Column( "T Start-T End", format("h:m:s"));
dtMain << New Column( "Day Length", format("h:m:s"));
dtMain << New Column( "Mid T", format("h:m:s"));
dtMain << New Column( "Twilight time", format("h:m:s"));
dtMain << New Column( "DT/TT", character);

// Loop through Main table and set values
DT = "";
dl = twTime = .;
For( i = 1, i <= N Rows( dtMain ), i++,
	If( i == 1 | Day( dtMain:Timestamp[i] ) != Day( dtMain:Timestamp[i - 1] ),
		theRow = Try(
			(dtLookUp << get rows where(
				dtLookUp:Month 2 == Month( dtMain:Timestamp[i] ) & dtLookUp:Date == Day( dtMain:Timestamp[i] )
			))[1],
			.
		)
	);
	
	// Get the time for the current row
	theTime = Date MDY( 12, 31, 1899 ) + 3600 * Hour( dtMain:Timestamp[i] ) + 60 * Minute( dtMain:Timestamp[i] )
	+Second( dtMain:Timestamp[i] );
	theNextTime = Date MDY( 12, 31, 1899 ) + 3600 * Hour( dtMain:Timestamp[i + 1] ) + 60 *
	Minute( dtMain:Timestamp[i + 1] ) + Second( dtMain:Timestamp[i + 1] );
	
	// If this is a Twilight Start, record the time, and set the value for Twilight time
	If( dtLookUp:Twilight start[theRow] >= theTime & dtLookUp:TwilightStart[theRow] <= theNextTime,
		dtMain:"T Start-T End"n[i] = dtLookUp:Twilight start[theRow];
		DT = "TT";
		twTime = dtLookUp:Twilight Length[theRow];
	);
	
	// If this is Sunrise, recorde the time and set the value for Day Length
	If( dtLookUp:Sunrise[theRow] >= theTime & dtLookUp:Sunrise[theRow] <= theNextTime,
		dtMain:"S Start-S End"n[i] = dtLookUp:Sunrise[theRow];
		DT = "DT";
		dl = dtLookUp:Day Length[theRow];
	);
	
	// If sunset has ended record the value and blank out the day length time
	If( dtLookUp:Sunset[theRow] >= theTime & dtLookUp:Sunset[theRow] <= theNextTime,
		dtMain:"S Start-S End"n[i] = dtLookUp:Sunset[theRow];
		DT = "TT";
		dl = .;
	);
	
	// If twighlight has ended record the value and blank out the Twilight time
	If( dtLookUp:Twilight End[theRow] >= theTime & dtLookUp:Twilight End[theRow] <= theNextTime,
		dtMain:"T Start-T End"n[i] = dtLookUp:Twilight End[theRow];
		DT = "";
		twTime = .;
	);
	
	// If this is the Mid T time, record the value 
	If( dtLookUp:Mid T[theRow] >= theTime & dtLookUp:Mid T[theRow] <= theNextTime,
		dtMain:Mid T[i] = dtLookUp:Mid T[theRow]
	);
	
	// Write the repeating values
	dtMain:"DT/TT"n[i] = DT;
	dtMain:Twilight time[i] = twTime;
	dtMain:Day Length[i] = dl;
	
	// Do some cleanup on ending records
	If( Is Missing( dtMain:"S Start-S End"n[i] ) == 0 & Is Missing( dtMain:Day Length[i - 1] ) == 0,
		dtMain:Day Length[i] = dtMain:Day Length[i - 1]
	);
	If( Is Missing( dtMain:"T Start-T End"n[i] ) == 0 & Is Missing( dtMain:Twilight time[i - 1] ) == 0,
		dtMain:Twilight time[i] = dtMain:Twilight time[i - 1]
	);
	If( Is Missing( dtMain:Twilight time[i] ) == 0 & dtMain:"DT/TT"n[i] == "",
		dtMain:"DT/TT"n[i] = dtMain:"DT/TT"n[i - 1]
	);
);

Re: Fill rows in main table depending on conditions in second table

SymS — Wed, 26 Jan 2022 17:06:32 GMT

Nelson,

Amazing! Thanks a lot!!!

Need to know where to start to practice scripting. Are there basic scripting examples which we could follow and DIY?

Re: Fill rows in main table depending on conditions in second table

jthi — Wed, 26 Jan 2022 17:27:40 GMT

Here are some suggestions:

When you do something with JMP, check out the script JMP provides in different locations (red triangle menu, source table script, enhanced log in JMP16+)
JMP Scripting Guide from JMP Documentation (and JSL Syntax Reference)
Scripting Index directly from JMP Help menu
Scripting Guide in JMP Help web page
Browse JMP Community
Mastering JMP Webinars (some of these might be using old versions of JMP and not the newest possible techniques available), but here is one example: Automating Analyses Using JMP Scripts
There are at least couple of books available: Jump into JMP Scripting and JSL Companion Applications of the JMP Scripting Language, Second Edition
JMP Scripting Users Group in JMP Community