- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Join tables with like method (contains)
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
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||'%'
]\"
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
thank you for your answer,
how do I install SQL in JMP?
I keep getting this error:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
jmp12
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
It could be that JMP doesn't offer that function, but I'm not sure about that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
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;" );
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Join tables with like method (contains)
thank you