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

[#1]

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

[#2 · Accepted solution]

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);

[#3]

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

[#4]

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. 

[#5]

@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

[#6]

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

[#7]

@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

[#8]

Hi @txnelson, could you advice?

[#9]

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".

[#10]

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