topic Re: Quick way to filter data table in Discussions

Quick way to filter data table

msharp — Fri, 04 Dec 2015 22:14:19 GMT

I'm not sure why, but there is no Outer Joins in JMP that I can find. These are helpful if you want to filter one data table by another data table. Because of this, all I've come up with is the below script but it's very slow in large data tables (1 million+ rows). Any suggestions to improve the script would be helpful.

dt << Clear Select;

for(i=1, i <= nitems(filter_list), i++,

dt << Select Where(Column(dt, "filter_col")[] == filter_list, current selection("extend"))

);

dt << Delete Rows;

Re: Quick way to filter data table

Jeff_Perkinson — Wed, 19 Oct 2016 01:19:54 GMT

You can use the Include Non-matches options in the Tables->Join dialog to get outer joins.

Will that work for you?

Re: Quick way to filter data table

msharp — Wed, 19 Oct 2016 01:20:03 GMT

I want either of the two on the bottom right.

Re: Quick way to filter data table

Jeff_Perkinson — Wed, 19 Oct 2016 01:20:14 GMT

Use the Match Flag option to add a column that indicates which tables contributed to a row in the resulting table. You can delete rows with a 3 in this column.

From the documentation:

• If a one (1) appears in this column, the data originated from the first (active) table.
• If a two (2) appears in this column, the data originated from the second table.
• If a three (3) appears in this column, the data was found in both the first and second tables.

Re: Quick way to filter data table

msharp — Mon, 07 Dec 2015 22:37:52 GMT

Thank You! While this adds a few extra steps (select where, delete rows) it gets the job done! Nicely.

It would be nice in a future version if there was an Exclude Matches box that would automatically remove all '3' match flags. Just a thought.