Choose Language Hide Translation Bar
brandon_mcrv
Level I

Get column values using Loop

Hi, I am a beginner, currently learning jsl scripting. I would like to know how shall i write a script (loop) to check N columns of values (names) and only output those that with a valid name, and ignore those "NA"? The end result (Column "All members") in each row shall be a combined list of valid names (ignoring column value with "NA"). See example below:- 

 

Col 1Col 2 Col 3Col 4…………….Col nAll members
JaneNADonTom AmyJane, Don, Tom, Amy
NAHenryTerryNA NAHenry, Terry
NANANAJack BobJack, Bob
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Get column values using Loop

Here are 3 different methods to solve the problem

  1. This one loops through the rows and columns, using the column names to come up with the answer
    Names Default To Here( 1 );
    
    // Create an Example data table
    dt = New Table( "Example 1",
    	Add Rows( 3 ),
    	New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
    	New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
    	New Column( "Col 3",
    		Character,
    		"Nominal",
    		Set Values( {"Don", "Terry", "NA"} )
    	),
    	New Column( "Col 4",
    		Character,
    		"Nominal",
    		Set Values( {"Tom", "NA", "Jack"} ),
    		Set Display Width( 48 )
    	),
    	New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
    );
    
    // Get the names of the columns in the data table
    colNames = dt << get column names( string );
    
    // Create the new column
    dt << New Column( "All Members", character );
    
    // Loop through each row
    For( k = 1, k <= N Rows( dt ), k++,
    	// Loop through all columns finding non NA values
    	For( i = 1, i <= N Items( colNames ), i++,
    		If( Column( colNames[i] )[k] != "NA",
    			If( :All Members[k] == "",
    				:All Members[k] = Column( colNames[i] )[k],
    				:All Members[k] = :All Members[k] || ", " || Column( colNames[i] )[k]
    			)
    		)
    	)
    );
  2. This one loops through the rows and columns using the column numbers to solve the problem
    Names Default To Here( 1 );
    
    // Create an Example data table
    dt = New Table( "Example 2",
    	Add Rows( 3 ),
    	New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
    	New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
    	New Column( "Col 3",
    		Character,
    		"Nominal",
    		Set Values( {"Don", "Terry", "NA"} )
    	),
    	New Column( "Col 4",
    		Character,
    		"Nominal",
    		Set Values( {"Tom", "NA", "Jack"} ),
    		Set Display Width( 48 )
    	),
    	New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
    );
    
    // Get the number of current columns
    numCol = N Cols( dt );
    
    // Create the new column
    dt << New Column( "All Members", character );
    
    // Loop through each row
    For( k = 1, k <= N Rows( dt ), k++,
    	// Loop through all columns finding non NA values
    	For( i = 1, i <= numCol, i++,
    		If( Column( i )[k] != "NA",
    			If( :All Members[k] == "",
    				:All Members[k] = Column( i )[k],
    				:All Members[k] = :All Members[k] || ", " || Column( i )[k]
    			)
    		)
    	)
    );
  3. This one changes the NA values in the data table, to blanks, which is the normal way to indicate missing values for character columns, and then uses a built in function called "Combine Columns()" to create the desired column
    Names Default To Here( 1 );
    
    // Create an Example data table
    dt = New Table( "Example 3",
    	Add Rows( 3 ),
    	New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
    	New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
    	New Column( "Col 3",
    		Character,
    		"Nominal",
    		Set Values( {"Don", "Terry", "NA"} )
    	),
    	New Column( "Col 4",
    		Character,
    		"Nominal",
    		Set Values( {"Tom", "NA", "Jack"} ),
    		Set Display Width( 48 )
    	),
    	New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
    );
    
    // Get the names of the columns in the data table
    colNames = dt << get column names( string );
    
    // Loop through the columns and delete the NA values
    For( i = 1, i <= N Cols( dt ), i++,
    	dt << select where( as column( colNames[i] ) == "NA" );
    	// set the found cells to a blank
    	try( column( colNames[i] )[ dt << get selected rows ] = "" )
    );
    
    // Create the new column using a built in function
    dt << Combine Columns(
    	delimiter( "," ),
    	Columns( eval(colNames) ),
    	Selected Columns are Indicator Columns(0 ),
    	Column Name( "All Members" )
    );
    
    // Move the column to the last position
    dt << Move Selected Columns( :All Members, To last );

 

Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Get column values using Loop

Here are 3 different methods to solve the problem

  1. This one loops through the rows and columns, using the column names to come up with the answer
    Names Default To Here( 1 );
    
    // Create an Example data table
    dt = New Table( "Example 1",
    	Add Rows( 3 ),
    	New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
    	New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
    	New Column( "Col 3",
    		Character,
    		"Nominal",
    		Set Values( {"Don", "Terry", "NA"} )
    	),
    	New Column( "Col 4",
    		Character,
    		"Nominal",
    		Set Values( {"Tom", "NA", "Jack"} ),
    		Set Display Width( 48 )
    	),
    	New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
    );
    
    // Get the names of the columns in the data table
    colNames = dt << get column names( string );
    
    // Create the new column
    dt << New Column( "All Members", character );
    
    // Loop through each row
    For( k = 1, k <= N Rows( dt ), k++,
    	// Loop through all columns finding non NA values
    	For( i = 1, i <= N Items( colNames ), i++,
    		If( Column( colNames[i] )[k] != "NA",
    			If( :All Members[k] == "",
    				:All Members[k] = Column( colNames[i] )[k],
    				:All Members[k] = :All Members[k] || ", " || Column( colNames[i] )[k]
    			)
    		)
    	)
    );
  2. This one loops through the rows and columns using the column numbers to solve the problem
    Names Default To Here( 1 );
    
    // Create an Example data table
    dt = New Table( "Example 2",
    	Add Rows( 3 ),
    	New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
    	New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
    	New Column( "Col 3",
    		Character,
    		"Nominal",
    		Set Values( {"Don", "Terry", "NA"} )
    	),
    	New Column( "Col 4",
    		Character,
    		"Nominal",
    		Set Values( {"Tom", "NA", "Jack"} ),
    		Set Display Width( 48 )
    	),
    	New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
    );
    
    // Get the number of current columns
    numCol = N Cols( dt );
    
    // Create the new column
    dt << New Column( "All Members", character );
    
    // Loop through each row
    For( k = 1, k <= N Rows( dt ), k++,
    	// Loop through all columns finding non NA values
    	For( i = 1, i <= numCol, i++,
    		If( Column( i )[k] != "NA",
    			If( :All Members[k] == "",
    				:All Members[k] = Column( i )[k],
    				:All Members[k] = :All Members[k] || ", " || Column( i )[k]
    			)
    		)
    	)
    );
  3. This one changes the NA values in the data table, to blanks, which is the normal way to indicate missing values for character columns, and then uses a built in function called "Combine Columns()" to create the desired column
    Names Default To Here( 1 );
    
    // Create an Example data table
    dt = New Table( "Example 3",
    	Add Rows( 3 ),
    	New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
    	New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
    	New Column( "Col 3",
    		Character,
    		"Nominal",
    		Set Values( {"Don", "Terry", "NA"} )
    	),
    	New Column( "Col 4",
    		Character,
    		"Nominal",
    		Set Values( {"Tom", "NA", "Jack"} ),
    		Set Display Width( 48 )
    	),
    	New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
    );
    
    // Get the names of the columns in the data table
    colNames = dt << get column names( string );
    
    // Loop through the columns and delete the NA values
    For( i = 1, i <= N Cols( dt ), i++,
    	dt << select where( as column( colNames[i] ) == "NA" );
    	// set the found cells to a blank
    	try( column( colNames[i] )[ dt << get selected rows ] = "" )
    );
    
    // Create the new column using a built in function
    dt << Combine Columns(
    	delimiter( "," ),
    	Columns( eval(colNames) ),
    	Selected Columns are Indicator Columns(0 ),
    	Column Name( "All Members" )
    );
    
    // Move the column to the last position
    dt << Move Selected Columns( :All Members, To last );

 

Jim

View solution in original post

Highlighted
brandon_mcrv
Level I

Re: Get column values using Loop

Hi txnelson, I really appreciate for your willingness to share multiple solutions! The scripts work like a charm and really thanks !
Article Labels