cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
lehaofeng
Level V

How to avoid the query not yielding results when the missing value is null in multi-criteria matching?

When join tables with more than one matching condition, for example, matching according to USL, LSL and Nominal in two tables, the lookup will be empty when there is only upper limit or only lower limit, i.e., when the lower limit is a missing value or the upper limit is a missing value, how to solve this solution? Are all the missing values replaced with 9999?

 

Thanks!

4 REPLIES 4
jthi
Super User

Re: How to avoid the query not yielding results when the missing value is null in multi-criteria matching?

Could you provide some example tables? If both tables have same values I think the joins should work fine

jthi_0-1698309573440.png

 

-Jarmo
lehaofeng
Level V

Re: How to avoid the query not yielding results when the missing value is null in multi-criteria matching?

Thanks, I know what you mean.
But my data table has more than 110,000 rows and only 1400 rows are having problems connecting, three of the four match conditions are missing values, I change the missing value to 9999 and it matches, after checking for a long time it didn't.
In addition, there are also many cases where the three matching conditions are missing, but still connected!

WebDesignesCrow
Super User

Re: How to avoid the query not yielding results when the missing value is null in multi-criteria matching?

What if you replace the missing value to 9999 (recode null to 9999) before join-matching?

hogi
Level XII

Re: How to avoid the query not yielding results when the missing value is null in multi-criteria matching?

Join with multiple match conditions always checks ALL matches and just generates a row when there is a match for all conditions.


If there are missing values in one of the tables, a match means: missing in both tables (see examples in  @jthi reply).

 

Stated differently:  join with missing value doesn't mean: 

If a value is missing, ignore this match condition for the specific row.