286284_en-US

liliconcom

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.

Advanced Statistical ModelingAutomation and ScriptingConsumer and Market ResearchData Blending and CleanupQuality and Process EngineeringRe: 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 Guide.....you 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;
         
         runit();
        )
       ),
       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",
   mySetID
   ++);
  
  // Set the value of the new column
  dt:SetID[i] = mySetID;
 );
);