Hello all, I have two dt and I want to create a new column in the primary dt based on the data from the secondary dt. I want a JSL command that check the date in "dt1" date column and match a group number from "dt2" based on the start and end date. (timeframe) dt1: (primary) dateGroup 5/17/2019 13:06 5/17/2019 13:06 5/23/2019 10:02 5/24/2019 13:47 dt2: (secondary) Groupstart dateend date15/17/2019 20:155/18/2019 8:1425/18/2019 8:155/18/2019 20:1415/18/2019 20:155/19/2019 8:1435/19/2019 8:155/19/2019 20:1425/19/2019 20:155/20/2019 8:1435/20/2019 8:155/20/2019 20:1425/20/2019 20:155/21/2019 8:1445/21/2019 8:155/21/2019 20:1435/21/2019 20:155/22/2019 8:1445/22/2019 8:155/22/2019 20:1435/22/2019 20:155/23/2019 8:1415/23/2019 8:155/23/2019 20:1445/23/2019 20:155/24/2019 8:1415/24/2019 8:155/24/2019 20:14 Your help will be highly appreciated.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Add a group number to a DT based on a different DT.

ileshem

Hello all,
I have two dt and I want to create a new column in the primary dt based on the data from the secondary dt.
I want a JSL command that check the date in "dt1" date column and match a group number from "dt2" based on the start and end date. (timeframe)

dt1: (primary)

date	Group
5/17/2019 13:06
5/17/2019 13:06
5/23/2019 10:02
5/24/2019 13:47

dt2: (secondary)

Group	start date	end date
1	5/17/2019 20:15	5/18/2019 8:14
2	5/18/2019 8:15	5/18/2019 20:14
1	5/18/2019 20:15	5/19/2019 8:14
3	5/19/2019 8:15	5/19/2019 20:14
2	5/19/2019 20:15	5/20/2019 8:14
3	5/20/2019 8:15	5/20/2019 20:14
2	5/20/2019 20:15	5/21/2019 8:14
4	5/21/2019 8:15	5/21/2019 20:14
3	5/21/2019 20:15	5/22/2019 8:14
4	5/22/2019 8:15	5/22/2019 20:14
3	5/22/2019 20:15	5/23/2019 8:14
1	5/23/2019 8:15	5/23/2019 20:14
4	5/23/2019 20:15	5/24/2019 8:14
1	5/24/2019 8:15	5/24/2019 20:14

Your help will be highly appreciated.

Accepted answers

jules

Hi again, @ileshem,

I mentioned you could solve this with custom jsl and wanted to share one way to do that with a few For() loops that work row by row through your primary table to find a date that meets your criteria in the secondary table. There are ways to do this more efficiently for very large applications but this is a start.

dt1 = Data Table( "primary.jmp" );
dt2 = Data Table( "secondary.jmp" );


For( i = 1, i <= N Row( dt1 ), i++, 

	For( j = 1, j <= N Row( dt2 ), j++, 
	
		If( And( dt1:date[i] >= dt2:Start Date[j], dt1:date[i] <= dt2:End Date[j] ),
			dt1:Group[i] = dt2:Group[j];
			Break();
		)
	
		
	)
	
);

@jules

primary.jmp

secondary.jmp

All comments

jules

Hi @ileshem,

There are a few ways I would go about this. One way would be to create a table that lists the groups for every time point between the start and end date, and then you could use that table with Tables > Update to bring the group number in by matching directly on that new date column that spans the start and end. That would involve creating that new spanned table, and depending on how many date ranges you have that table could get pretty large.

Another option (that I have a preference for in this case) is to use Tables > JMP Query Builder, which will allow us to establish a join that can use some SQL logic to match where a date is both after a start date but before the end date. You can design this query interactively, which is one reason I like this approach, so if you didn't want to script something, you could do it all with your mouse. Saving the script results in some jsl that isn't too hard to generalize, just requires some trickery to get just table names without extension, and to insert the table names using evalInsert(). A little strange, and others might have a more elegant way to do what I did here. That said, it's one way to go without writing some custom JSL to go row by row in one table to do the lookup in another (which is a third, and totally acceptable approach as well).

Here's the code for JMP Query Builder, and I attached your example tables for trying this out. If you're interested in how to set this up interactively let me know -- it takes all of 30 seconds to build this from scratch.

dt1 = Data Table( "primary.jmp" );
dt2 = Data Table( "secondary.jmp" );

dt1NameOnly = Left( dt1 << getname, Length( dt1 << getname ) - 4 );
dt2NameOnly = Left( dt2 << getname, Length( dt2 << getname ) - 4 );

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables( Eval Insert( "\[ [dt1<<getname => "_MEMORY_", dt2<<getname => "_MEMORY_"] ]\" ) ),
	QueryName( "SQLQuery1" ),
	Select( Column( "date", "t1", Numeric Format( "m/d/y h:m", "-1", "NO", "" ) ), Column( "Group 2", "t2" ) ),
	From(
		Table( dt1NameOnly, Alias( "t1" ) ),
		Table(
			dt2NameOnly,
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				GE(
					Column( "date", "t1", Numeric Format( "m/d/y h:m", "-1", "NO", "" ) ),
					Column( "Start Date", "t2", Numeric Format( "m/d/y h:m", "-1", "NO", "" ) )
				) & LE(
					Column( "date", "t1", Numeric Format( "m/d/y h:m", "-1", "NO", "" ) ),
					Column( "End Date", "t2", Numeric Format( "m/d/y h:m", "-1", "NO", "" ) )
				)
			)
		)
	)
) << Run;

secondary.jmp

primary.jmp

jules

Hi again, @ileshem,

dt1 = Data Table( "primary.jmp" );
dt2 = Data Table( "secondary.jmp" );


For( i = 1, i <= N Row( dt1 ), i++, 

	For( j = 1, j <= N Row( dt2 ), j++, 
	
		If( And( dt1:date[i] >= dt2:Start Date[j], dt1:date[i] <= dt2:End Date[j] ),
			dt1:Group[i] = dt2:Group[j];
			Break();
		)
	
		
	)
	
);

Thanks allot!!