Get column values using Loop

brandon_mcrv

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 1	Col 2	Col 3	Col 4	…………….	Col n	All members
Jane	NA	Don	Tom		Amy	Jane, Don, Tom, Amy
NA	Henry	Terry	NA		NA	Henry, Terry
NA	NA	NA	Jack		Bob	Jack, Bob

txnelson

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 );