topic Re: create a weight Column in Discussions

create a weight Column

gianpaolo — Tue, 16 Jan 2018 09:25:35 GMT

Good morning.

using JSL code, starting from a column with GOOD / BAD rows, i would like to create a new WEIGHT COLUMN, where BAD =1 and GOOD = Ratio BAD/GOOD.

There is JSL to do it quickly?

thanks in advance

Gianpaolo

Re: create a weight Column

uday_guntupalli — Tue, 16 Jan 2018 13:29:56 GMT

Can you kindly provide sample data of your column to understand what GOOD / BAD looks like ?

Re: create a weight Column

gianpaolo — Wed, 17 Jan 2018 09:02:22 GMT

Split Weight Split where:
GOOD 0.4 BAD=1
BAD 1 GOOD = #BAD/#GOOD
GOOD 0.4 in this case 4/10 =0.4
GOOD 0.4
GOOD 0.4
GOOD 0.4
GOOD 0.4
GOOD 0.4
GOOD 0.4
GOOD 0.4
GOOD 0.4
BAD 1
BAD 1
BAD 1

Re: create a weight Column

txnelson — Wed, 17 Jan 2018 16:09:48 GMT

Here are two similar solutions.

This one generates the new column in open JSL

Names Default To Here( 1 );

// Create the sample data table
dt = New Table( "Good Bad Ratio",
	Add Rows( 14 ),
	New Column( "Good_Bad",
		Character,
		"Nominal",
		Set Values(
			{"GOOD", "BAD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD",
			"GOOD", "BAD", "BAD", "BAD"}
		)
	)
);

// Create the new column using a formula for the calculation
dt << New Column( "Weight",
	formula(
		If( Row() == 1,
			TheGoodCount = Col Number( If( :Good_Bad == "GOOD", 1, . ) );
			TheBadCount = Col Number( If( :Good_Bad == "BAD", 1, . ) );
			Ratio = TheBadCount / TheGoodCount;
		);
		If(
			:Good_Bad == "GOOD", Ratio,
			:Good_Bad == "BAD", 1
		);
	)
);

This version uses a column formula to create the values for the column Weight

Names Default To Here( 1 );

// Create the sample data table
dt = New Table( "Good Bad Ratio",
	Add Rows( 14 ),
	New Column( "Good_Bad",
		Character,
		"Nominal",
		Set Values(
			{"GOOD", "BAD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD", "GOOD",
			"GOOD", "BAD", "BAD", "BAD"}
		)
	)
);

// Calculate the Ratio
TheGoodCount = Col Number( If( dt:Good_Bad == "GOOD", 1, . ) );
TheBadCount = Col Number( If( dt:Good_Bad == "BAD", 1, . ) );
Ratio = TheBadCount / TheGoodCount;

// Add the new column and populate it
dt << New Column( "Weight" );
For Each Row(
	If(
		dt:Good_Bad == "GOOD", dt:Weight = Ratio,
		dt:Good_Bad == "BAD", dt:Weight = 1
	)
);

Re: create a weight Column

gianpaolo — Wed, 17 Jan 2018 16:26:22 GMT

Great Jim, Thanks very much.

i will adapt your code to my job