topic How to use column name in the formula ? in Discussions

How to use column name in the formula ?

ENTHU — Sat, 10 Jun 2023 23:27:54 GMT

I have a table as below.I need to find the sum of (TA008+SA2900 to SA2950+SA3400 to SA3500+TA99) for every item.How can have jsl do this?

Item	SA0100	SA0801	SA0804	SA0817	SA0818	SA0871	SA0874	SA2930	SA2950	SA2982	SA2983	SA2984	SA3403	SA3404	SA3422	SA3423	SA3424	SA3452	SA3474	SA3476	SA3477	SA3491	SA3534	SA9706	SA9787	SA9798	SA9901	SA9908	TA001	TA008	TA029	TA034	TA035	TA088	TA097	TA099
8A630	12		2																									7	12	2						7
8A631	18	1	4			1																						11	18	6						11
8A632	12		1		3	1																						21	12	5						21
A633	81		1		2	2																			1			13	81	5					1	13
A629	249			1		2																			1			33	249	3					1	33
A626	380				2	3																						13	380	5						13
A600	11		1				1																	1				3	11	2					1	3

Re: How to use column name in the formula ?

txnelson — Fri, 26 Mar 2021 19:49:53 GMT

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Sums",
	formula(
		Sum(
			:SA2930,
			:SA2950,
			:SA3403,
			:SA3404,
			:SA3422,
			:SA3423,
			:SA3424,
			:SA3452,
			:SA3474,
			:SA3476,
			:SA3477,
			:SA3491,
			:TA008,
			:TA099
		)
	)
);

The above will add a new column to your data table with the sum being added from the required columns into the new column

Re: How to use column name in the formula ?

ENTHU — Fri, 26 Mar 2021 20:52:41 GMT

The data set is dynamic.I could have columns with names SA3400 to SA3450 and SA2900 to SA2950.I would have to mention over 100 column names if I followed this approach.Trying to fins a way to simplify.

Re: How to use column name in the formula ?

txnelson — Fri, 26 Mar 2021 21:30:01 GMT

Ah......that requirement was not stated in the original question.

However, the code to do what you want is repetitive, but not difficult.

names default to here(1);
dt=current data table();

colNames = dt << get column names(string);

sumList = "";

if(contains(colNames,"TA008"),
	sumList = ":TA008";
);
For(i=2900, i<=2950, i++,
	If( contains(colNames,"SA" || char(i)),
		if( sumList != "", sumList = sumList || ",");
		sumList = sumList || ":SA" || char(i);
	)
);
For(i=3400, i<=3500, i++,
	If( contains(colNames,"SA" || char(i)),
		if( sumList != "", sumList = sumList || ",");
		sumList = sumList || ":SA" || char(i);
	)
);
if(contains(colNames,"TA099"),
	if( sumList != "", sumList = sumList || ",");
	sumList = sumList || ":TA099";
);
Eval(
	Parse(
		"dt << new column(\!"Sums\!",formula(sum( " || sumList ||
		" )));"
	)
);

Re: How to use column name in the formula ?

Georg — Sun, 28 Mar 2021 13:19:49 GMT

Here's just another way how to deal with columns to make a formula:

// expression way
cdt=current data table();
sum_lst2 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
	colname = Column( col_lst2[i] ) << get name;
	If( Contains( {"TA008", "TA099"}, colname ) | (colname >= "SA2900" & colname <= "SA2950") | (colname >= "SA3400" & colname <= "SA3550"),
		Insert Into( sum_lst2, col_lst2[i] )
	);
);
Eval( Eval Expr( cdt << New Column( "Sums_expr", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );