topic Re: Join tables with like method (contains) in Discussions

Join tables with like method (contains)

shiro — Sat, 10 Jun 2023 23:42:22 GMT

hey, is it possible to join tables not with equal sign (=) but with like method (%) or something similar?
for example: in one table I have column of names, in the second table I have column of parts of those name and i want them to match
"america"--> "ame", "eric" ect...

Re: Join tables with like method (contains)

txnelson — Tue, 11 Jan 2022 15:20:21 GMT

One way that could work to do this, would be to create a new column in the matching data table, and to use the contains within the formula in that column. If the target column contains "ame" and "eric" then the new column would be given the value "america". Then the joining would be completed by matching with the new formula column.

Re: Join tables with like method (contains)

jthi — Tue, 11 Jan 2022 15:58:56 GMT

You could also use Query (but I would most likely formula + update/join method):

Names Default To Here(1);

dt1 = New Table("Untitled",
	Add Rows(4),
	New Column("Col", Character, "Nominal", Set Values({"america", "america", "test", "test"})),
	New Column("Val", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4]))
);

dt2 = New Table("Untitled",
	Add Rows(2),
	New Column("Col", Character, "Nominal", Set Values({"ame", "rica"})),
	New Column("Val", Numeric, "Continuous", Format("Best", 12), Set Values([11, 22]))
);

wait(1);
dt3 = Query(
	Table(dt1, "t1"),
	Table(dt2, "t2"), 
	"\[SELECT *
	FROM t1
	LEFT OUTER JOIN t2 ON t1.col like '%'||t2.col||'%'
	]\"
);

Re: Join tables with like method (contains)

shiro — Tue, 11 Jan 2022 19:39:25 GMT

thank you for your answer,

how do I install SQL in JMP?

I keep getting this error:

Re: Join tables with like method (contains)

jthi — Wed, 12 Jan 2022 05:21:53 GMT

I think that should work by default in JMP without SQL installations. Which JMP version are you using?

SQL Functions (jmp.com) , Write a SQL Query (jmp.com)

Re: Join tables with like method (contains)

shiro — Wed, 12 Jan 2022 09:12:30 GMT

jmp12

Re: Join tables with like method (contains)

jthi — Wed, 12 Jan 2022 10:31:11 GMT

It could be that JMP doesn't offer that function, but I'm not sure about that.

Re: Join tables with like method (contains)

txnelson — Wed, 12 Jan 2022 11:11:28 GMT

The SQL Query functionality was added in JMP 13

The following syntax failed in JMP 12 but executed without error in JMP 13

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
result = Query( dt, "SELECT * FROM 'Big Class' WHERE age > 14;" );

Re: Join tables with like method (contains)

shiro — Wed, 12 Jan 2022 19:17:20 GMT

thank you