Subscribe Bookmark RSS Feed

Quick way to filter data table

msharp

Super User

Joined:

Jul 28, 2015

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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.

10600_matcolsdial.png

-Jeff
4 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

10596_matcolsdial.png

Will that work for you?

-Jeff
msharp

Super User

Joined:

Jul 28, 2015

I want either of the two on the bottom right.

10597_pastedImage_0.png

Solution

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.

10600_matcolsdial.png

-Jeff
msharp

Super User

Joined:

Jul 28, 2015

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.