topic join tables based on a substring (whole word as part of a string) in Discussions

join tables based on a substring (whole word as part of a string)

ron_horne — Tue, 25 Jul 2023 10:26:15 GMT

dear members of the community,
i have 2 tables. one containing strings and another containing a list of words and numbers.
i would like to join the table based on whether the string includes the exact word. but not if it pat of another (longer) word.
for example 'cat' should match only 'cat' and not 'bigcat' as in the image below.
i have added spaces before and after the terms in order to facilitate. in sql i could use a join command based on LIKE to preform a match based on a substring so adding the spaces helps identify whole words to a certain extent.

thank you for any suggestions

Re: join tables based on a substring (whole word as part of a string)

jthi — Tue, 25 Jul 2023 10:43:44 GMT

You can use SQL to join tables in JMP using Query (Query(<<dt1|Table(dt1, alias1)>, ..., <dtN, aliasN)>>, <private | invisible>, <scalar>, sqlStatement )). Part of example from scripting index:

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;
      ]\"
);