topic Re: Compare two tables and retreive relevant rows from one table based on the other. in Discussions

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

SymS — Sat, 10 Jun 2023 23:34:02 GMT

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

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

txnelson — Sun, 18 Jul 2021 07:30:38 GMT

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.

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

SymS — Mon, 19 Jul 2021 20:53:22 GMT

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.

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

brady_brady — Tue, 20 Jul 2021 01:47:20 GMT

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;