Issue with conditional formatting (color) using 'for each row' and nested 'for' loop

[#1]

Hello,

I'm on the tail end of a script where I've summarized a bunch of data in various columns (both numeric and character).

Looping to format numeric cells meeting certain criteria is easy enough but I'm having some issue assigning a color for character columns that contain a certain substring.

For instance, a cell (with the following content:1,1,2,1,1,2,3,3) should be colored red because it contains at 1 occurrence of the character '3'.

If I specify the entire content of the cell, no problem but I try to look for '3' and color accordingly ->fails.

I've added the scripts below.

Any suggestions would be appreciated.

Best,

Sebastien 

Names Default To Here( 1 );
clear log();

dt = New Table( "test",
	Add Rows( 8 ),

	New Column( "unique_group_id",
		Character,
		"Nominal",
		Set Values(
			{"ELN-19-0588-A", "ELN-19-0588-B", "ELN-19-0588-C", "ELN-19-0588-D",
			"ELN-19-0588-E", "ELN-19-0588-F", "ELN-19-0588-G", "ELN-19-0588-H"}
		),
		Set Display Width( 115 )
	),
	New Column( "FOB max",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"0,0,0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0",
			"0,0,3,0,0,1,0,2", "1,1,2,1,1,2,3,3", "0,0,0,0,0,0,0,0",
			"4,2,2,3,2,3,3,4", "1,0,0,1,1,1,1,1"}
		)
	),
	New Column( "glib1 % PBS - potatoe",
		Numeric,
		"Ordinal",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [100, 113.39, 108.3, 156.38, 822.21, 768.51, 487.2, 222.56] )
	),
	New Column( "calb (IHC) cere",
		Character,
		"Nominal",
		Set Values(
			{"0,0,0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0",
			"0,0,0,0,0,0,0,0", "3,4,3,4,3,3,3,4", "3,3,3,5,2,3,3,3",
			"0,0,0,0,0,0,0,0", "0,0,0,0,0,0,1,0"}
		)
	)
);
wait(2);


col=dt <<get column names;
show(col);
nc=n items(col);
show(nc);
//This first example works fine 

c="% PBS";

For(i=1,i<=nc,i++, if( Contains( col[i], c ),
	for each row(
	If(  as column(col[i])>200,		 
		 column(col[i]) << color cells( "Light Red", Row())
	)
)));


//Does not work - it needs to be an exact match 
c="FOB";
b={"3"};//trying to look for cells in columns labelled with FOB whihc contains the character '3'
For(i=1,i<=nc,i++, if( Contains( col[i], c ),
	for each row(
	If(  contains(b,as column(col[i])),
		 column(col[i])<< color cells( "Light Red", Row())
	)
)));

wait (2);

//Works when specifying exactly the content of the cell(s) to match
c="FOB";
b={"1,1,2,1,1,2,3,3"};

For(i=1,i<=nc,i++, if( Contains( col[i], c ),
	for each row(
	If(  contains(b,as column(col[i])),
		 column(col[i])<< color cells( "Red", Row())
	)
)));

[#2 · Accepted solution]

You were specifying your Contains() function backwards and you were making your comparisons JMP Lists.  Fix those and it works fine.

Names Default To Here( 1 );
clear log();

dt = New Table( "test",
	Add Rows( 8 ),

	New Column( "unique_group_id",
		Character,
		"Nominal",
		Set Values(
			{"ELN-19-0588-A", "ELN-19-0588-B", "ELN-19-0588-C", "ELN-19-0588-D",
			"ELN-19-0588-E", "ELN-19-0588-F", "ELN-19-0588-G", "ELN-19-0588-H"}
		),
		Set Display Width( 115 )
	),
	New Column( "FOB max",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"0,0,0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0",
			"0,0,3,0,0,1,0,2", "1,1,2,1,1,2,3,3", "0,0,0,0,0,0,0,0",
			"4,2,2,3,2,3,3,4", "1,0,0,1,1,1,1,1"}
		)
	),
	New Column( "glib1 % PBS - potatoe",
		Numeric,
		"Ordinal",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [100, 113.39, 108.3, 156.38, 822.21, 768.51, 487.2, 222.56] )
	),
	New Column( "calb (IHC) cere",
		Character,
		"Nominal",
		Set Values(
			{"0,0,0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0", "0,0,0,0,0,0,0,0",
			"0,0,0,0,0,0,0,0", "3,4,3,4,3,3,3,4", "3,3,3,5,2,3,3,3",
			"0,0,0,0,0,0,0,0", "0,0,0,0,0,0,1,0"}
		)
	)
);
wait(2);


col=dt <<get column names(string);
show(col);
nc=n items(col);
show(nc);
//This first example works fine 

c="% PBS";

For(i=1,i<=nc,i++, if( Contains( col[i], c ),
	for each row(
	If(  as column(col[i])>200,		 
		 column(col[i]) << color cells( "Light Red", Row())
	)
)));


//Does not work - it needs to be an exact match 
c = "FOB";
b = "3";//trying to look for cells in columns labelled with FOB whihc contains the character '3'
For( i = 1, i <= nc, i++,
	If( Contains( col[i], c ),
		For Each Row(
			If( Contains( As Column( col[i] ), b ),
				Column( col[i] ) << color cells( "Light Red", Row() )
			);
		)
	)
);

wait (2);

//Works when specifying exactly the content of the cell(s) to match
c = "FOB";
b = "1,1,2,1,1,2,3,3";

For( i = 1, i <= nc, i++,
	If( Contains( col[i], c ),
		For Each Row(
			If( Contains( As Column( col[i] ), b ),
				Column( col[i] ) << color cells( "Red", Row() )
			)
		)
	)
);

[#3]

Thanks!

[#4]

Hi,

Change your code to match line 67 in the code below.

Contains ( ) lets you do a couple of different things, depending on the arguments you supply. For example:

Contains ( {"cat", "dog", "mouse", "dog", "horse"}, "dog" ) returns 2, because the first occurrence of "dog" in supplied list is at position 2.

Contains ( {"cattle", "dog", "mouse"}, "cat") returns 0, as "cat" does not occur in the list. If we want to search the list element by element to see whether "cat" is a substring of any element, we have to iterate through the list.

Your original code is a bit like this last example: Contains (b, :col) checks whether the column cell value matches any element in b, which you've defined as {"3"}. No match is found.

Conversely, the new code, Contains(:col, b[1]), checks whether b[1], the first element of list b, which is "3", is contained in the column cell value. Used this way, the Contains (<<whole>>, <<part>>) function looks for substring <<part>> within string <<whole>>--which is what I believe you want.

Cheers,

Brady

Contains ( {"cat", "dog", "mouse"}, "cat") returns