BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
Rini_Kar

Community Trekker

Joined:

Sep 10, 2017

How to replicate the same formula for other columns

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 AgeAdmission dateDischarge dateOrder dateService 1 Service 2Service 3Service 1_1
122513/2/20121/4/20121/3/20120101
122513/2/20121/4/201223/3/20121111
131727/8/201531/10/20153/9/20150112
131727/8/201531/10/201517/9/20151102
131727/8/201531/10/201528/10/20151002
14451/1/201613/2/201631/1/20160011
14451/1/201613/2/20168/2/20161101
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: How to replicate the same formula for other columns

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

 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

 

 

Jim
2 REPLIES 2
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: How to replicate the same formula for other columns

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

 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

 

 

Jim
Rini_Kar

Community Trekker

Joined:

Sep 10, 2017

Re: How to replicate the same formula for other columns

Thanks Jim. The solution worked well.