Returning value for one column based on reference from other column

[#1]

Hi All 

I have the columns "Rel Interval", "SrID", "Pass_Fail". I want to create and generate script for the last column "Updated Pass_Fail_SrID". All SrID are passing at rel interval "0" and "24" but SrID "B" and "D" fail at "100". I want to create last column "Updated Pass_Fail_SrID" which takes reference of pass and fail and assigns pass / fail to all checkpoints. This is so that while plotting I can have the split reference.    

Any pointers are greatly appreciated. 

Many thanks 

Satej

 

[#2 · Accepted solution]

Hi Satej,

The simplest solution is to recode your Pass_Fail column as a Continuous numerical column with Fail = 0 and Pass = 1. Then you can just look for the minimum by Sr ID to determine if any of the Sr ID failed at any time.

See this solution attached

Best,

TS

[#3 · Accepted solution]

One note: the recoded Pass Fail needs to be numerical but it can be nominal or ordinal too

[#4]

Thanks so much for the prompt responses. Thats quite elegant way

[#5]

Below is a simple script that will create the new column by creating a second table, manipulating that table and then bringing the results back into the original table.  While this is done using a script, each of the steps in the script, are available to be performed using the interactive point and click methodology that JMP does so well.

names default to here(1);
// create the example data table
dt = New Table( "Example 3",
	Add Rows( 15 ),
	New Column( "Rel Interval",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 0, 0, 0, 0, 24, 24, 24, 24, 24, 100, 100, 100, 100, 100] )
	),
	New Column( "Sr ID",
		Character( 8 ),
		"Nominal",
		Set Values(
			{"A", "B", "C", "D", "E", "A", "B", "C", "D", "E", "A", "B", "C", "D",
			"E"}
		)
	),
	New Column( "Pass_Fail",
		Character( 8 ),
		"Nominal",
		Set Values(
			{"Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Pass",
			"Pass", "Pass", "Fail", "Pass", "Fail", "Pass"}
		)
	)
);
wait(3);  // this is added just so the viewer can see the original data table

// Split the Pass Fail column into 2 columns
dtSplit = dt << Split(
	Split By( :Pass_Fail ),
	Split( :Pass_Fail ),
	Group( :Sr ID ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);
wait(2); 
// Create a new column that has a formula that will set the Pass or Fail value
dtSplit << New Column("Updated Pass_Fail_SrID", character, formula(Word( 1, :Fail || "," || :Pass, "," )));

wait(2);
// convert the formula column into static values
dtSplit:Updated Pass_Fail_SRID << delete formula;

wait(2);
// Delete no longer wanted columns
dtSplit << delete columns("Fail","Pass");

wait(2);
// Update the original data table with the information from the split table
dt  << Update(
	With( dtSplit ),
	Match Columns( :Sr ID = :Sr ID )
);

Here are the steps to do this interactively

1. Click on the data table that has the columns, Rel Interval, Sr ID, and Pass_Fail, to make the data table the current active data table
2. Go to the pull down menus and select              Tables==>Split
3. Set the Pass_Fail column and the "Split By" choice, and also as the "Split Columns" choice, and then set the Sr ID column as the "Group" choice
4. Click on OK
5. Go to the new data table that has been created
6. Create a new column called "Updated Pass_Fail_SrID" and make It's data type, "Character"
7. Right click on the column header and select "Formula"
8. In the formula window, type in the formula    Word( 1, :Fail || "," || :Pass, "," )         This formula will concatenate the 2 columns of Fail and Pass together, placing a comma between the values.  It will then use the Word() function to select the 1st word in the concatenated list, and since the rows that have no Fails, will have the value of Pass as it's first word, the resulting values will set the correct values.
9. Click on OK
10. Now go to the Col Info dialog window for the new column and click on the column property "formula", and then click on Remove.  This will convert the values in the new column to static values, rather that calculated values.
11. Delete the columns Pass and Fail.  They are no longer needed.
12. Go back to the original data table and go to the pull down menu and select       Tables==Update
13. Select the lookup table the new column has been created in as the table to use to get the update from
14. check the box for "Match columns"
15. select Sr ID from both data tables and then click on the Match Button
16. Click OK and your original data table will now have the new column added to it

[#6]

Thanks so much for the prompt responses.