topic Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column? in Discussions

How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 02:05:11 GMT

For example, the name column of Big Class.jmp automatically sets the color gradient of the name based on the value of the height column
It is required to be done in script.

Thanks!

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

txnelson — Wed, 18 Oct 2023 04:39:09 GMT

Here is the only way I know how to do it

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
a = {1, 3, 5};
b = {2, 4, 6};
:height << color cells( {{"Red", a}, {"blue", b}} );
theScript = Char( :height << get script );
theScript = Substr( theScript, Contains( theScript, "Color Cells" ) );
theScript = ":Name<<" || Substr(
	theScript,
	1,
	Contains( theScript, "Set Value" ) - 3
);
Eval( Parse( theScript ) );

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 05:15:26 GMT

Thank Jim!

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
m= 4 ;Column(m)<<set property("Color Gradient",{"Green to White to Red",Range({col min(Column(m)),col max(Column(m)),col mean(Column(m))})})<<Color Cell by Value;

//？？
theScript = Char( :height << get script );
theScript = Substr( theScript, Contains( theScript, "Color Cells" ) );
theScript = ":Name<<" || Substr(
	theScript,
	1,
	Contains( theScript, "Set Value" ) - 3
);
Eval( Parse( theScript ) );

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

txnelson — Wed, 18 Oct 2023 07:32:44 GMT

I do not have a solution for a gradient color pattern.

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 07:51:50 GMT

Thank Jim!

How do I get the fill color of a cell using JSL?

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

jthi — Wed, 18 Oct 2023 09:01:57 GMT

One place where you could get the color is from report html. You will have to perform conversion though

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
m = 4;
Eval(EvalExpr(
	Column(dt, m) << set property(
		"Color Gradient",
		{"Green to White to Red", Range({Expr(Col Min(Column(m))), Expr(Col Max(Column(m))), Expr(Col Mean(Column(m)))})}
	) << Color Cell by Value;	
));

(dt << get as report) << get html;

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 09:16:14 GMT

Complex I will not operate, can use the script to achieve?

Thanks Experts!

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

jthi — Wed, 18 Oct 2023 09:41:04 GMT

Also depending on how the gradient is created, you could maybe use Value Colors instead of Gradient column property. Then you can get the colors from column properties.

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 09:48:18 GMT

txt = dt << get as report;
txt << Save HTML( "c:\tt.html" );
u = "c:\tt.html";
For( k = 0, k <= 60, k++,
	Try( d1 = Open( u, HTML Table( k, Column Names( 1 ), Data Starts( 2 ) ) ) );
	Wait( 0 );
);

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 10:24:53 GMT

Ask experts how to use RE to get these content?

How do I set the color in the name column after I get this?

Thanks!

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 10:53:56 GMT

EmEditor

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

jthi — Wed, 18 Oct 2023 17:09:47 GMT

Lots of assumptions made in this script and it definitely isn't robust

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
m = 4;
Eval(
	Eval Expr(
		Column(dt, m) << set property(
			"Color Gradient",
			{"Green to White to Red", Range(
				{Expr(Col Min(Column(m))), Expr(Col Max(Column(m))), Expr(Col Mean(Column(m)))}
			)}
		) << Color Cell by Value(1);
	)
);

// Lots of assumptions made in this solution

str = (dt << get as report) << get html;
// get just <table> and drop first row (headers)
l = Words(Regex(str, "<table.*</table>"), "\!N");
Remove From(l, 1,2);
Remove From(l, N Items(l));

// Get bgcolors 
colors = {};
For Each({line}, l,
	str = Regex(line, "bgcolor=#(.*?)>", "\1");
	Insert Into(colors, str);
);

// Convert colors
// Option 1 (might work?)
jmp_colors = Transform Each({color}, colors, Hex To Number(color) * -1);

// Option 2
jmp_colors2 = Transform Each({color}, colors, 
	r = Hex To Number(Left(color, 2));
	g = Hex To Number(Substr(color, 3, 2));
	b = Hex To Number(Right(color, 2));
	RGB Color({r,g,b});
);

// Set colors

// One row at the time
For Each Row(
	:name << Color Cells(jmp_colors[Row()], Row());
);

// Or create list of lists and use that
color_list = Transform Each({jmp_color, idx}, jmp_colors2,
	Eval List({jmp_color, idx});
);
Eval(EvalExpr(:age << Color Cells(Expr(color_list))));

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Wed, 18 Oct 2023 23:49:25 GMT

be of no effect?

l = Words(Regex(str, ""), "\!N");

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Fri, 20 Oct 2023 07:27:28 GMT

I used the sentence JSL didn't get it right, for some reason, JMP 17

Thanks!

str = (dt << get as report) << get html;
// get just  and drop first row (headers)
l = Words(Regex(str, ""), "\!N");
Remove From(l, 1,2);
Remove From(l, N Items(l));

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

jthi — Fri, 20 Oct 2023 07:34:06 GMT

Your regex pattern is incorrect

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

lala — Fri, 20 Oct 2023 08:21:13 GMT

Eval(EvalExpr(:age << Color Cells(Expr(color_list))));

How do I modify this JSL to be a loop?

Thank you for your help!

For( j = 1, j <= N Row( dt ), j++,
	dt[j, "age"] << color_list( {j} )//?
);

Re: How to use JSL to set the color gradient of a column in text format by the value of a numeric column?

jthi — Fri, 20 Oct 2023 08:32:09 GMT

You can get the idea from Transform Each I used or use the other method with For Each Row