Returning value for one column based on reference from other column

sbidwe — Sat, 10 Jun 2023 23:15:30 GMT

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

Re: Returning value for one column based on reference from other column

Thierry_S — Wed, 01 Jul 2020 04:47:20 GMT

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,

Re: Returning value for one column based on reference from other column

Thierry_S — Wed, 01 Jul 2020 04:48:17 GMT

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

Re: Returning value for one column based on reference from other column

txnelson — Wed, 01 Jul 2020 04:53:35 GMT

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

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
Go to the pull down menus and select Tables==>Split
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
Click on OK
Go to the new data table that has been created
Create a new column called "Updated Pass_Fail_SrID" and make It's data type, "Character"
Right click on the column header and select "Formula"
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.
Click on OK
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.
Delete the columns Pass and Fail. They are no longer needed.
Go back to the original data table and go to the pull down menu and select Tables==Update
Select the lookup table the new column has been created in as the table to use to get the update from
check the box for "Match columns"
select Sr ID from both data tables and then click on the Match Button
Click OK and your original data table will now have the new column added to it

Re: Returning value for one column based on reference from other column

sbidwe — Wed, 01 Jul 2020 05:11:58 GMT

Thanks so much for the prompt responses.

Re: Returning value for one column based on reference from other column

sbidwe — Wed, 01 Jul 2020 05:13:16 GMT

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

topic Re: Returning value for one column based on reference from other column in Discussions

Returning value for one column based on reference from other column

Re: Returning value for one column based on reference from other column

Re: Returning value for one column based on reference from other column

Re: Returning value for one column based on reference from other column

Re: Returning value for one column based on reference from other column

Re: Returning value for one column based on reference from other column