Compare two tables and retreive relevant rows from one table based on the other.

Question

I have 2 tables.

Table 1:  Timestamp (in 15 minute intervals),i1,i2,i3,p1,p2,p3,p4,.... (182 columns)

Table 2: Year, Month, Date, StartTime, EndTime ( StartTime and EndTime is in the format- h:m am/pm)

I would like to select all 182 columns and only the rows in Table1 which lies between StartTime and EndTime of Table2 and make a subset.

Note:StartTime and EndTime for every date in Table2 could lie anywhere-does not exactly match the Timestamp in Table1.(or in other words StartTime and EndTime is not a multiple of 15)

How can I accomplish this 1) using point and click method- Like Joins and using "Row-Select where". or other inbuilt formulas...step by step and 2) JSL

brady_brady · Answer

Hi,

If I understand your question, this would be well beyond agonizing to perform interactively. I take it that you want a subset of the original table, where a row is part of the subset as long as it falls between the begin and end time of ANY row in the begin/end table.

If that is what you would like, the script below will do it. It relies on the evalinsert( ) function to build a string in a legitimate date/time format that JMP can convert to a number natively using the informat( ) command... so be sure to check out these 2 functions in the scripting index.

By default, select where ( ) pre-clears any current row selection. The optional argument current selection ("extend") allows us to select additional rows without losing the prior selection--this allows us to use a loop to make selections incrementally.

Cheers,

Brady

Names Default To Here(1);

////////////////////Build example tables
dt = astable(J(1000,1,randomuniform(today()-3600*24*60, today() + 3600*24*60)), << column names({"Timestamp"}));
dt:Timestamp << Input Format( "ddMonyyyy h:m:s", 0 ) << Format( "ddMonyyyy h:m:s", 22, 0 );
dt << Sort( By( :Timestamp ), Replace Table, Order( Ascending ) );

yearMat = J(20,1,2021);
monList = words("May,Jun,Jul,Aug,Sep",",")[J(20,1,randominteger(1,5))];
dayMat = J(20,1,randominteger(1,30));
amMat = J(20,1,7*3600 + randominteger(0,20)*60);
pmMat = J(20,1,16*3600 + randominteger(0,20)*60);
dt2 = new table("lookups",
	<<new column("Year", <<set values(yearMat)),
	<<new column("Month", character, <<set values(monList)),
	<<new column("Day", <<set values(dayMat)),
	<<new column("Start", <<set values(amMat), Input Format( "h:m" ), Format( "h:m", 10 )),
	<<new column("End", <<set values(pmMat), Input Format( "h:m" ), Format( "h:m", 10 ))
);

//////////////////////////////end table building

//begin processing. the idea is to cycle through all rows of the lookup table, 
//selecting matching rows in the original table
dt << clear select;

for(r = 1, r<= nrow(dt2), r++,

//piece together beginning and ending date/time strings using evalinsert(), 
	//and use informat() to convert to a numeric value
	begin_time = informat(evalinsert("^dt2:day[r]^^dt2:month[r]^^dt2:year[r]^ ^format(dt2:Start[r], \!"h:m\!")^"));
	end_time = informat(evalinsert("^dt2:day[r]^^dt2:month[r]^^dt2:year[r]^ ^format(dt2:End[r], \!"h:m\!")^"));

//select cells in the main table with a timestamp in this range. 
	//the current selection ("extend") option augments the current selection, instead of replacing it (the default behavior)
	dt << select where (begin_time <= :timeStamp < end_time, current selection("extend"));
);

dtSub = dt << subset(selected rows(1), selected columns(0));

dt << clear select;

SymS · Answer

Table 1: Like this I have 15 minute interval data for the whole year.

Table 2: Some times this table has all the dates in the month. Here in the sample there is just a few in each month.

Jim, Let me know if this is helpful.

txnelson · Answer

I am not finding your description of your  data tables clear enough to allow me to see exactly what their structures are.  Could you please provide a few sample rows and columns for each of the data tables.  That would really be helpful.