Most efficient way to add information to rows by matching the time stamp from two data tables?

scottahindle

I extracted Historian data at 5 second intervals over a long time period, ending up with a data table of many many rows. Initially in this data table I had two columns (call this table "2205 - 2209 PD3102" and I referenced to it as dtPDTag):

1. Date/time stamp

2. Data value (for the Historian tag)

In a different data table (dt in the below JSL syntax) I had the start and end time of productions for which I had the PO identifier and recipe symbol. (So four columns: 1. Start date/time, 2. End date/time, 3. PO number, 4. Recipe symbol.)

 

In dtPDTag I added two columns - PO and Symbol - into which I wanted to add the PO number and recipe symbol (based on the date/time stamp). The JSL code I used to fulfil this task is below, but I'm assuming this is a pretty inefficient way to manage this task.  (My computer didn't seem to respond too healthily to the demand placed upon it!) Would be great to hear of better / more effective ways to approach such a task in the future.

By "most efficient" I refer to a more effective scripting method than the combination of two for loops and the one "if" condition seen in my JSL below.

 

 

Names Default To Here(1);

dt = Current Data Table();
nr = N Rows(dt); // show(nr);

lstPO = dt:PO << Get Values;
lstSymbol = dt:Symbol << Get Values;
lstStartTime = dt:StartTime << Get Values;
lstEndTime = dt:EndTime << Get Values; // show(lstPO,lstSymbol,lstStartTime,lstEndTime);

dtPDTag = data Table( "2205 - 2209 PD3102" );
nrPD = N Rows(dtPDTag);  //  show(nrPD);

For(j=1,j<=nr,j++,

 _start = lstStartTime[j]; _end = lstEndTime[j];  // show(_start,_end);
 _PO =lstPO[j]; _symbol = lstSymbol[j];

	For(i=1,i<=nrPD,i++,

		If( ( dtPDTag:DateTime[i] > lstStartTime[j] ) & ( dtPDTag:DateTime[i] <  lstEndTime[j] ),
			
			dtPDTag:PO[i] = lstPO[j];
			dtPDTag:Symbol[i] = lstSymbol[j];

		,
			

		);
		
	);

);

 

txnelson

Here is my approach.  If the data are sorted, then they can be passed through one time and the values needed can be found.  Take a look at the JSL below for the general approach to take.  The code is not tested, I do not have data tables to test it against.  However, I have used this approach many times before, and it is an efficient approach.  But be prepared to adjust it a bit.

Names Default To Here( 1 );

dt = Current Data Table();
nr = N Rows( dt );

dtPDTag = Data Table( "2205 - 2209 PD3102" );
nrPD = N Rows( dtPDTag );

// Sort both data tables by the timestamps to allow the tables to only 
// have to be read once

// Before sorting the data, if the tables need to be returned to the 
// original order then create a new column in each table that represents
// the current Row number, which then will allow the table to be sorted
// back to the original order

// dt << New Column( "_row_", set each value( Row() ));
// dtPDTag << New Column( "_row_", set each value( Row() ));

dt << sort( By( :StartTime, :EndTime ), order( ascending, ascending ), replace table( 1 ) );
dtPDTag << sort( By( :DateTime ), order( ascending ), replace table( 1 ) );

PDStart = 1; // Variable that keeps track of where in the dtPDTag table to start to find values

For( dtI = 1, dtI <= nr, dtI++,
	For( dtPDI = PDStart, dtPDI <= nrPD, dtPDI++,
		If( ( dtPDTag:DateTime[dtPDI] > dt:StartTime[dtI] ) & ( dtPDTag:DateTime[dtPDI] <  dt:EndTime ),
			dtPDTag:PO[dtPDI] = dt:PO[dtI];
			dtPDTag:Symbol[dtPDI] = dt:Symbol[dtI];
			PDStart = dtPDI;
			Break();
	)
);

// Resort to original if required and delete the _row_ column
// dt << sort( By( _row_ ), order( ascending ), replace table( 1 ) );
// dt << delete columns( :_row_ );
// dtPDTag << sort( By( _row_ ), order( ascending ), replace table( 1 ) );
// dtPDTag << delete columns( :_row_ );