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