Bin a defect using a priority list

[#1]

I have attached table1 which consist of 3 input columns (Part No, Yield and Defects) and 1 output column (Bin). I have also attached another priority_table stating the hierarchy of the defects with cracks as the most important.

How do I generate the output column Bin, using JMP scripting where if a Part No has a few defects, put the most important defect in the Bin column for all the rows concerning that Part No.? Also if the yield is less than 60% and the defect is not on the  priority_table than bin into "less than 60%"?

Below are defects among other words. The Defects column is keyed in by operators.

[#2]

I cannot be 100% sure how you want to do the matching, but I can guess that you will match with match in Defects (last word) based on Bin values. There are many ways to solve this, here are two possible ideas:

Here is one a bit more interactive solution:

1. Create new column to Table1 with only the last word of Defects column
2. Join Table1 with priority_table using the new column from Table1 and Bin from priority_table
3. Use Col Min formula to get highest priority for Part No (change Priority to numeric first)
4. From here you could join priority table again with the new formula column and priority from priority_table to get the final bin (except for missing ones)
5. Create final formula to calculate the missing value

You should get all scripts required for these steps from JMP (table scripts (and enhanced log if using JMP16))

Or if you are familiar with SQL, you could try using it directly. Below is some sort of query:

Names Default To Here(1);

dt_table1 = Open("Table1.jmp", Invisible);
dt_priority = Open("priority_table.jmp", Invisible);

dt_result = Query(
	Table(dt_table1, "t1"),
	Table(dt_priority, "p"),
	"\[
	with min_prio as (SELECT t1."Part No", min(p.priority) as priority
       FROM t1
       LEFT OUTER JOIN p
           ON SUBSTR(t1.Defects, LOCATE(" ", t1.defects) + 1) = p.Bin
       group by t1."Part No"
    )
    select t1."Part No", t1.Yield, t1.Defects, mp.priority,
    case when p.Bin is NULL and t1.yield < 60 then "less than 60%" else p.Bin end as Bin
    FROM t1
		LEFT OUTER JOIN min_prio mp on mp."Part No" = t1."Part No"
		left outer join p on mp.priority = p.priority
      ]\"
);

dt_result << Set Name("SQL_RESULT");

[#3]

Thanks Jarmo,

I updated my question and Table1.jmp. The Defects column is keyed in manually by operators and the defects words like cracks, stringers...etc are among each string. 

Thanks.

[#4 · Accepted solution]

You can maybe change the join in SQL to use wildcards:

Names Default To Here(1);

dt_table1 = Open("Table1.jmp", Invisible);
dt_priority = Open("priority_table.jmp", Invisible);

dt_result = Query(
	Table(dt_table1, "t1"),
	Table(dt_priority, "p"),
	"\[
	with min_prio as (SELECT t1."Part No", min(p.priority) as priority
       FROM t1
       LEFT OUTER JOIN p
           on t1.Defects like "%"||p.Bin||"%"
       group by t1."Part No"
    )
    select t1."Part No", t1.Yield, t1.Defects, mp.priority,
    case when p.Bin is NULL and t1.yield < 60 then "less than 60%" else p.Bin end as Bin
    FROM t1
		LEFT OUTER JOIN min_prio mp on mp."Part No" = t1."Part No"
		left outer join p on mp.priority = p.priority
      ]\"
);

dt_result << Set Name("SQL_RESULT");

Because the text is entered manually by operators there will be typos and this cannot catch them. For something like that, you will most likely have to start doing some sort of distance calculation between strings (one example: Levenshtein distance ). In JMP you can also use Shortest Edit Script for "distance" calculation.

Text Explorer might also be able to help you, but I'm not familiar with that platform

[#5]

Hi Jarmo,

Thanks for the code. I want to change the criteria and the code to make sure I understand. If I want to change :

Top priority is 

1) Any parts not in the priority list, move to a bin called "Invalid Defects"

2) any parts is <60%, move to a bin called "<60% parts"

3) Compare the priority list

I changed the code like below but was having syntax error :

dt_result = Query(
	Table(dt_table1, "t1"),
	Table(dt_priority, "p"),
	"\[
	with min_prio as (SELECT t1."Part No", min(p.priority) as priority
       FROM t1
       LEFT OUTER JOIN p
           on t1.Defects like "%"||p.Bin||"%"
       group by t1."Part No"
    )
    select t1."Part No", t1.Yield, t1.Defects, mp.priority,
    case when p.Bin is NULL  then "Invalid Defect" else when t1.yield < 60 then "less than 60%"  else
    
    p.Bin end as Bin
    FROM t1
		LEFT OUTER JOIN min_prio mp on mp."Part No" = t1."Part No"
		left outer join p on mp.priority = p.priority
      ]\"
);

dt_result << Set Name("SQL_RESULT");

[#6]

You get fairly helpful error message

Query failed: near "when": syntax error in access or evaluation of 'Query'

Problem is with this line here:

    case when p.Bin is NULL then "Invalid Defect" else when t1.yield < 60 then "less than 60%"  else

SQLite CASE 

You can chain statements with when so seem to have have extra else there then "Invalid Defect" else when

[#7]

Got it, thanks!