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...
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.
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||'%'
]\"
);
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)
It could be that JMP doesn't offer that function, but I'm not sure about that.
