topic Re: Sorting and Adding a new column with formula in Discussions

Sorting and Adding a new column with formula

Jackie_ — Fri, 09 Jun 2023 19:55:25 GMT

Hi,

I want to sort column N Rows in descending order and add a new column which shows the % of total of N Rows. Please help me out with the jsl script

<< Summary(
	Group(
		:Open VIO,
		:Open SCLK,
		:Open SDATA,
		:Open USID,
		:Open RF1,
		:Open RF2,
		:Open RF3,
		:Open RF4,
		:Open RFC
	),
	Freq( "None" ),
	Weight( "None" )
)

Thanks,

Jack

Re: Sorting and Adding a new column with formula

Jackie_ — Wed, 25 Aug 2021 15:50:42 GMT

@txnelson

Re: Sorting and Adding a new column with formula

txnelson — Wed, 25 Aug 2021 19:08:32 GMT

Here is a script that works. Please note that since JMP will generate the jsl you need, when you run interactively, you should be able to create your own code.

Data Table( "original data" ) << Summary(
	Group(
		:Open VIO, :Open SCLK, :Open SDATA, :Open USID, :Open RF1, :Open RF2,
		:Open RF3, :Open RF4, :Open RFC
	),
	"% of Total"n,
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 ),
	output table( "Summarized Table" )
);
Data Table( "Summarized Table") << sort(by(:N Rows), order(descending), replace table(1));

Re: Sorting and Adding a new column with formula

ih — Wed, 25 Aug 2021 19:11:46 GMT

The scripting index under the Help menu has some useful information for you here. I searched for 'sort' this way:

If you are happy with the formula you used for the % Total column, you can use the scripting index to find a function to create a new column in the data table. Or, you could use the add-in to install a function called 'Copy Column Script' to the cols menu. Then you can just select the column, copy the script, and paste that in your script. If I understand your requirement for that column though you could just divide the N Row column by the sum of the column N Row.

Putting that together might look like this:

Names default to here(1);

// The function from your example:
dtSummarized = dt << << Summary(
	Group(
		:Open VIO,
		:Open SCLK,
		:Open SDATA,
		:Open USID,
		:Open RF1,
		:Open RF2,
		:Open RF3,
		:Open RF4,
		:Open RFC
	),
	Freq( "None" ),
	Weight( "None" )
);

// dtSummarized = Current Data Table();

// From Scripting Index
dtSummarized << Sort( replace table, By( :N Rows ), Order( Descending ) );

// From Scripting Tools Add-in
New Column("% Total", Numeric, "Continuous", Format("Percent", 12, 2), Formula(:N Rows / Col Sum(:N Rows)), Set Display Width(44));