How to replace empty rows in select columns?

[#1]

Hello JMP Community,

I have a data table that columns have specific name, first i wrote the script to select those columns, and i want to replace those column's empty rows with "REF", but other columns without those specific name won't change, how can i do this with JSL? Thanks.

[#2]

Here is a little script that loops through all of the character columns and then in each column, finds all of the rows that have empty cells, and sets them to REF

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

// Get the column names for all character columns
colNameList = dt << get column names( string, character );

For( i = 1, i <= N Items( colNameList ), i++,
	Try(
		As Column( dt, colNameList[i] )[dt << get rows where( As Column( dt, colNameList[i] ) == "" )] =
		"REF"
	)
);

[#3]

Hi txnelson,

I tried the script you gave, but this will also replace value column, so can i only replace column with lot data?

I wrote this script but don't understand what's part be wrong.

dt = Current Data Table();
colNameList = dt << get column names( string, character );

For( i = 1, i <= N Items( colNameList ), i++,
	Try(
		If(Contains(Col_List[i], "::LotData")),
		As Column( dt, colNameList[i] )[dt << get rows where( As Column( dt, colNameList[i] ) == "" )] =
		"REF"
	)
);

[#4 · Accepted solution]

Hi Sing,

I think there are two issues in your Script,

1st: your if referenced the wrong list (Col_List)

2nd: the action is not inside your if statement, but after, so the if cannot control the action.

Try the attached script, it should do the job. In addition to Jim's script I added the modifications you may need.

Names Default To Here( 1 );

dt = New Table( "test",
	New Column( "A::LotData", "Character", set values( {"", "YES", ""} ) ),
	New Column( "B::LotData", "Character", set values( {"", "YES", ""} ) ),
	New Column( "C::LotData", "Character", set values( {"", "YES", ""} ) ),
	New Column( "D::Test", "Character", set values( {"", "Test", ""} ) ),
	New Column( "value", set values( {1, 2, 3} ) )
);

colNameList = dt << get column names( string, character );

For( i = 1, i <= N Items( colNameList ), i++,
	Try(
		If( Contains( colNameList[i], "::LotData" ),
			As Column( dt, colNameList[i] )[dt << get rows where( As Column( dt, colNameList[i] ) == "" )] = "REF"
		), 

	)
);

[#5]

Thanks a lot!

[#6]

Hi,

Another non-scripting solution is to select your three columns, right-click on the headers > Standardize Attributes > Recode > replace the blanks with "REF."

CAUTION: Recoding cannot be undone, so make sure to save your table before going through the steps above.

Best,

TS