How to replicate the same formula for other columns

Rini_Kar

Hi, 

I have similar data as attached below. I need to create a column for each of the service variable with formula: Col Sum (Service 1, ID, Admission date). I have close to 100 different service variables, so doing this manually is out of question. Only the first argument I pass to Col Sum will change and by variables (ID and Admission date) remains the same for every formula. Is there a way to use JSL script to create columns and dynamically pass the variable to function and assign column name with the variable used to differentiate the source and output. Service1_1 will be one of the ouput column. 

 

 

Thanks in advance.

 

ID	Age	Admission date	Discharge date	Order date	Service 1	Service 2	Service 3	Service 1_1
12	25	13/2/2012	1/4/2012	1/3/2012	0	1	0	1
12	25	13/2/2012	1/4/2012	23/3/2012	1	1	1	1
13	17	27/8/2015	31/10/2015	3/9/2015	0	1	1	2
13	17	27/8/2015	31/10/2015	17/9/2015	1	1	0	2
13	17	27/8/2015	31/10/2015	28/10/2015	1	0	0	2
14	45	1/1/2016	13/2/2016	31/1/2016	0	0	1	1
14	45	1/1/2016	13/2/2016	8/2/2016	1	1	0	1

txnelson

Here is a script that I think will help

Names Default To Here( 1 );
dt = Current Data Table();
nc = N Cols( dt );
For( i = 6, i <= nc, i++,
	Eval(
		Substitute(
				Expr(
					dt << New Column( __newname__,
						formula( Col Sum( __oldname__, :ID, :Admission date ) )
					)
				),
			Expr( __newname__ ), Column( i ) << get name || "_1",
			Expr( __oldname__ ), Parse( ":" || Char( Column( dt, i ) << get name ) )
		)
	)
);<BR /><BR />

 Additionally, you can do this interactively by using 

     Col==>New Columns

and specify as many new columns as you need.  Then, input the formula for the 1st of the new columns.  Finally, select that column, and all of the other new columns and then go to

     Col==>Standardize Attributes

Select

     Column Properties==>Formula

Click on the check box "Substitute Column Reference"

and then Click "OK"

It will copy the formula from the first new column, to all of the other columns, substituting the Service N column