Get column values using Loop

brandon_mcrv — Fri, 09 Jun 2023 23:30:37 GMT

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

Re: Get column values using Loop

txnelson — Tue, 30 Jun 2020 09:22:37 GMT

Here are 3 different methods to solve the problem

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

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

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

Re: Get column values using Loop

brandon_mcrv — Mon, 06 Jul 2020 08:19:29 GMT

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

topic Re: Get column values using Loop in Discussions

Get column values using Loop

Re: Get column values using Loop

Re: Get column values using Loop