topic Re: How to create column formula using a column name from a list in Discussions

How to create column formula using a column name from a list

Jackie_ — Fri, 09 Jun 2023 16:04:17 GMT

Hi,

I want to create a new column in the 2nd JMP table with a formula that simply sums the Columns contain in the list (in the code I've assigned column name to binss.
For Ex: The list contains Column names "39","41","43". I want to create a new Column in the Soft Bin Summary table with addition of all these in the formula

I can't figure out what I'm doing wrong in going from the list {""} of columns to the reference to the columns.

Any help is much appreciated.

Here's the code that I've tried

Names Default To Here( 1 );
Clear Globals();

dt = Data Table( "Untitled 910" );
dt2 = Data Table( "Soft Bin Summary" );

Try( For Each Row( :Yield SummaryBins = Word( 1, :Yield SummaryBins, "[]" ) ) );
grouplist = dt:Yield SummaryBins << get values;

For( i = N Items( grouplist ), i >= 1, i--,
	If( grouplist[i] == "",
		Remove From( grouplist, i, 1 ), 

	));
binss = {}; /// list contaiting bin value
For( i = 1, i <= N Items( grouplist ), i++, 

	If( Contains( grouplist[i], " " ), 

		Insert Into( binss, grouplist[i] )
	)
);

adad = Words( binss[1], " " );
F = Eval Expr( Sum( Expr( adad ) ) );

/// Something doesn't seem to right in the below loop

For( i = 1, i <= N Items( binss ), i++, 	
dt2 << New Column( binss[i], "Numeric", "Continuous", Format( "Percent", 12, 2 ), Formula( Name Expr( F ) ) ););

Thanks,

Jack

Re: How to create column formula using a column name from a list

txnelson — Fri, 09 Dec 2022 15:29:24 GMT

You were running into an issue where the rows with multiple column names were not being handled correctly. Here is my attempt at solving your issue.

Names Default To Here( 1 );
Clear Globals();

dt = Data Table( "Untitled 910" );
dt2 = Data Table( "Soft Bin Summary" );

current data table(dt);

// Build execution string
sumString = "";
for each row(
	if(:Yield SummaryBins != "",
		i=1;
		while(word(i,:Yield SummaryBins,"[] ") !=  "",
			sumString = sumString || ", :\!"" || word(i,:Yield SummaryBins,"[] ") || "\!"n";
			i++;
		)
	)
);
// Remove first comma
sumString = substr(sumString, 1);

// Execute new column string
eval(parse("dt2<<new column(\!"Sum Column\!", formula(sum(" || sumString || ")));"));

// Add the format for the column
dt2:Sum Column << format("Percent", 7, 2);

Re: How to create column formula using a column name from a list

Jackie_ — Fri, 09 Dec 2022 15:50:06 GMT

Thanks Jim.

Your code works. I want to create separate columns for each group.

For ex: In the below case, There are two groups Opens and Shorts which contains respective column name.

I want to have two different columns with a formula that sums the respective values

Column 1 (Sum of Column (39, 41, 43)) and Column 2 (Sum of Column (38, 40, 42)) in the Summary table

Re: How to create column formula using a column name from a list

txnelson — Fri, 09 Dec 2022 16:08:58 GMT

It is a matter of just expanding on the code I provided.

Names Default To Here( 1 );
Clear Globals();

dt = Data Table( "Untitled 910" );
dt2 = Data Table( "Soft Bin Summary" );

Current Data Table( dt );

// Build execution string
openString = shortsString = "";
For Each Row(
	If( :Yield SummaryBins != "",
		If( :Yield SummaryLabel == "Opens",
			i = 1;
			While( Word( i, :Yield SummaryBins, "[] " ) != "",
				openString = openString || ", :\!"" || Word( i, :Yield SummaryBins, "[] " ) || "\!"n";
				i++;
			);
		);
		If( :Yield SummaryLabel == "Shorts",
			i = 1;
			While( Word( i, :Yield SummaryBins, "[] " ) != "",
				shortsString = shortsString || ", :\!"" || Word( i, :Yield SummaryBins, "[] " ) || "\!"n";
				i++;
			);
		);
	)
);
// Remove first comma
openString = Substr( openString, 1 );
shortsString = Substr( shortsString, 1 );

// Execute new column strings
Eval( Parse( "dt2<<new column(\!"Open Column\!", formula(sum(" || openString || ")));" ) );
Eval( Parse( "dt2<<new column(\!"Shorts Column\!", formula(sum(" || shortsString || ")));" ) );

// Add the format for the column
dt2:Open Column << Format( "Percent", 7, 2 );
dt2:Shorts Column << Format( "Percent", 7, 2 );

Please take the time to study the code provided and to respond back with additional questions when you do not understand how the code is working.

Re: How to create column formula using a column name from a list

Jackie_ — Tue, 03 Jan 2023 16:03:14 GMT

@txnelson Thank you. Much appreciated.
Can it be done dynamically? I've cases where there are more than 2 groups. Writing separate string execution function would be tedious.

I've attached a reference table below

Re: How to create column formula using a column name from a list

txnelson — Fri, 09 Dec 2022 17:22:17 GMT

Of course it can....what are the rules?

Re: How to create column formula using a column name from a list

Jackie_ — Tue, 03 Jan 2023 16:03:29 GMT

@txnelson The rules are similar to previous example.

Create each separate Column group with a formula that sums the respective bins.

I want to create a Column in the Soft Bin Summary table with addition of all Column name bins in the formula.

For ex: Create IDD Currents group Column with formula (Sum( Column ("52", "42","49") )),

Create Open group Column with formula (Sum( Column ("39", "41","43") ))

and like wise for the remaining label groups in the Yield Summary label column

Re: How to create column formula using a column name from a list

Jackie_ — Fri, 09 Dec 2022 22:42:07 GMT

Hi @txnelson, could you advice?

Re: How to create column formula using a column name from a list

txnelson — Sat, 10 Dec 2022 01:25:32 GMT

My read of your description is that the rule you have is to create a new column for each row in the Condition Table that has a value in the Yield SummaryLabel column.

So I suggest that you read through each row in the Conditions Table, and when you find a row with a value in the Yield SummaryLabel column, you create the new column.

There is one case where you have an issue. Row 14 which has SYL_Yield specified, it has a value of 1 for Yield SummaryBins. There is not a column called "1".

Re: How to create column formula using a column name from a list

Jackie_ — Sat, 10 Dec 2022 13:21:51 GMT

Row 14 can be excluded. Can you share the code? I just wanted to refer and get an idea

Re: How to create column formula using a column name from a list

txnelson — Sat, 10 Dec 2022 16:03:32 GMT

Names Default To Here( 1 );
dt = Data Table( "Condition table" );
dt2 = Data Table( "Soft Bin Summary" );

Current Data Table( dt );

// Rule: Create a new Summary Column for each row in the Condition Table
// that has a value in the Yield SummaryLabel column
For( i = 1, i <= N Rows( dt ), i++,
	If( dt:Yield SummaryLabel[i] != "",
		theString = "";
		k = 1;
		While( Word( k, dt:Yield SummaryBins[i], "[] " ) != "",
			theString = theString || ", :\!"" || Word(
				k,
				dt:Yield SummaryBins[i],
				"[] "
			) || "\!"n";
			k++;
		);
		theString = Substr( theString, 1 );
		Eval(
			Parse(
				"dt2<<new column(\!"" || dt:Yield SummaryLabel[i] ||
				"\!", formula(sum(" || theString ||
				")),Format( \!"Percent\!", 7, 2 ));"
			)
		);
	)
);

The only compensation that Community members get for helping other members, is the satisfaction that comes from the members they have helped becoming more self-sufficient. Cutting and pasting of someone else's working code is not a good way to become self-sufficient.