Fill rows in main table depending on conditions in second table

SymS

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?

Timestamp-1min-Result.jmp

Timestamp-1min.jmp

Start-End-TS1.jmp

txnelson

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]
	);
);