topic Re: Joining two tables based on dates in Discussions

Joining two tables based on dates

Yotam — Sat, 10 Jun 2023 23:44:31 GMT

Hi,

I want to accomplish something which I think is very simple: Joining tables based on dates.

Let's say I have the following tables:

Table1 - which has the following columns: Group1, event_date.

Table2 - which has the following columns: Group2, start_date, end_date.

I want the join to work as follows: (Group1 == Group2) AND (start_date < date < end_date)

How can I accomplish this using a JSL code?

Thanks

Re: Joining two tables based on dates

jthi — Tue, 22 Feb 2022 17:14:28 GMT

I think it will be easiest to Query for this and write some SQL for the join

Names Default To Here(1);
// Using aliases, performing a join
dtSAT = Open("$SAMPLE_DATA/SATByYear.jmp", Invisible);
dtUS = Open("$SAMPLE_DATA/US Demographics.jmp", Invisible);
Query(
	Table(dtSAT, "t1"),
	Table(dtUS, "t2"), 

	"\[SELECT t1.State, t1."SAT Math", t2."College Degrees",
            t2."Eighth Grade Math"
       FROM t1
       LEFT OUTER JOIN t2
           ON t1.State = t2.State
       WHERE t1.'SAT Math' > 550;
      ]\"
);

It isn't the only option but it should be fairly simple and easy to understand (if you know SQL).

Edit:

Depending on your table size, you could also perform left join (or full join) and then remove values which do not pass the date check:

Names Default To Here(1);

dt1 = New Table("Untitled",
	Add Rows(6),
	New Column("Column 1", Character, "Nominal", Set Values({"a", "a", "a", "b", "b", "b"})),
	New Column("date", Numeric, "Continuous", Format("Best", 12), Set Values([2, 3, 4, 2, 3, 5]))
);

dt2 = New Table("Untitled 2",
	Add Rows(2),
	New Column("Column 1", Character, "Nominal", Set Values({"a", "b"})),
	New Column("start", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1])),
	New Column("end", Numeric, "Continuous", Format("Best", 12), Set Selected, Set Values([3, 6]))
);

dt3 = dt1 << Join(
	With(dt2),
	By Matching Columns(:Column 1 = :Column 1),
	Drop multiples(0, 0),
	Include Nonmatches(1, 0),
	Preserve main table order(1)
);

dt3 << New Column("OK", Numeric, Nominal, << Set Each Value(:start < :date < :end));
dt3 << Select Where(:OK == 0);// << Delete Rows;

Re: Joining two tables based on dates

Yotam — Tue, 22 Feb 2022 19:47:35 GMT

Perfect! I didn't know you can work with SQL syntax within JMP. This will make my life a lot easier from now on.

Thank you

Re: Joining two tables based on dates

jthi — Tue, 22 Feb 2022 20:05:18 GMT

This help article might be helpful when using SQL in JMP Queries: SQL Functions Available for JMP Queries