topic Re: How to replace empty rows in select columns? in Discussions

How to replace empty rows in select columns?

SING — Sat, 10 Jun 2023 23:50:35 GMT

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.

Re: How to replace empty rows in select columns?

txnelson — Thu, 30 Jun 2022 04:42:32 GMT

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

Re: How to replace empty rows in select columns?

Thierry_S — Thu, 30 Jun 2022 04:50:04 GMT

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,

Re: How to replace empty rows in select columns?

SING — Thu, 30 Jun 2022 06:59:30 GMT

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

Re: How to replace empty rows in select columns?

Georg — Sat, 02 Jul 2022 20:57:37 GMT

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"
		), 

	)
);

Re: How to replace empty rows in select columns?

SING — Mon, 04 Jul 2022 01:49:30 GMT

Thanks a lot!