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
Jim