Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Level V

Is there an easy way to create set IDs for datasets within a data table?

I have an arbitrary number of columns that define the inputs to a process, and a bunch of other columns that are the outputs, and for each set of inputs there are a bunch of rows of output that are keyed on time.


Is it possible with .jsl to create a new "setid" column that starts with "1" in row 1, then takes the user selected columns (1 or more) and whenever any of the values in any of those columns changes, it increments the value in the setid column -- otherwise the setid stays the same.  We can assume the rows are sorted reasonably by set.

Super User

Re: Is there an easy way to create set IDs for datasets within a data table?

Here is a little example that you can start with.  I dug up some old code I did many many years ago, and modified it.  It first pops up a dialog box


And after selecting the columns you click on OK and it creates the new column.

If you have not read the Scripting now should do that.  In order to expand on this simple script, you will need to understand JSL and what you can do with it.  Here is the script

Clear Symbols();

Names Default To Here( 1 );
dt = Current Data Table();

allcolumnslist = dt << get column names( "string" );

nw = New Window( "Generate SETID Column",
	Border Box( Left( 3 ), top( 2 ),
		V List Box(
			Text Box( "Create a SETID Column" ),
			H List Box(
				V List Box(
					// Generate the master display box for the columns in the data table
					Panel Box( "Select Columns",
						colListData = Col List Box( width( 180 ), nLines( Min( N Cols( dt ), 15 ) ) ),
						colListData << append( dt << get column names( "string" ) ),    
						// At startup, sselect the columns in the selection box for 
						// the columns that are selected in the data table 
						For( II = 1, II <= N Items( All Columns list ), II++,
							If( Column( ii ) << get selected == 1,
								colListData << set selected( II )
				// Setup the selection buttons and the selection boxes
				Panel Box( "Selected Columns to Create SETID from",
					Lineup Box( N Col( 2 ), Spacing( 3 ),
						Button Box( "Add Column(s)", colListY << Append( colListData << GetSelected ) ),
						colListY = Col List Box( width( 180 ), nLines( 10 ), min col( 1 ), max col( 0 ) ), 

				// Setup the action buttons that control the window processing
				V List Box(
					Panel Box( "Action",
						Lineup Box( N Col( 1 ),
							Button Box( "OK",    
								// Check to see if the required data has been input
								If( N Items( colListY << GetItems ) == 0,
									Dialog( "No Y Columns Specified", Button( "OK" ) ),   
									// Process the Y Columns
									thecols = {};
									thecols = colListY << GetItems;
									nw << close window;
							Button Box( "Cancel", Stop() ),
							Text Box( " " ),
							Button Box( "Remove", colListY << RemoveSelected ),
							Button Box( "Help", notImplemented )


// Here is the function that calculates the new column.
runit = Function( {}, 

	If( Try( dt:SetID << get name, "" ) != "",
		dt << delete columns( {"SetID"} )
	dt << New Column( "SetID", numeric );
	// Set the value for row 1
	mySetID = 1;
	dt:SetID[1] = mySetID;
	// Loop across the remaining rows, checking for a change from the previous row
	For( i = 2, i <= N Rows( dt ), i++,
		thesame = "Yes";
		For( k = 1, k <= N Items( thecols ), k++,
			If( Column( dt, thecols[k] )[i] != Column( dt, thecols[k] )[i - 1],
				thesame = "No"
		// If a change is found, increase the counter
		If( thesame == "No",
		// Set the value of the new column
		dt:SetID[i] = mySetID;