870823_en-US

liliconcom

Loop to set a normalization formula for several columns

Hi community,

I'm trying to write a JSL script to set a normalization formula with dynamic column reference. 

 

What's wrong here?
"Mean(M4_pop)" & "Std Dev(M4_pop)" are fix columns that are to be used for the normalization. 

 

dt = Current Data Table();

numCols = dt << Get Column Names( Numeric, "String" );

For( i = 1, i <= N Items( numCols ), i++,

&nbsp;&nbsp; &nbsp; colName = numCols[i];&nbsp;&nbsp;

&nbsp;&nbsp;&nbsp; normColName = colName || "_norm";

&nbsp;&nbsp;&nbsp; dt << New Column(

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; normColName,

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Numeric,

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Continuous,

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Formula( (colName- :"Mean(M4_pop)"n) / :"Std Dev(M4_pop)"n)

&nbsp;&nbsp;&nbsp; );

&nbsp;&nbsp;&nbsp; );

 

Automation and ScriptingRe: Loop to set a normalization formula for several columns

You will have to evaluate colName to the formula
Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute 

Re: Loop to set a normalization formula for several columns

Hi David,

 

there might be prettier solutions but this is one way to make it work:

 

dt = Current Data Table();

numCols = dt << Get Column Names( Numeric, "String" );

For( i = 1, i <= N Items( numCols ), i++,

    colName = numCols[i];
     
    mean = "Mean(M4_pop)";
    
    std =  "Std Dev(M4_pop)";

    normColName = colName || "_norm";

    dt << New Column(

        normColName,

        Numeric,

        Continuous,

        Formula( (as column(colName)- as column (mean)) / as column(Std))

    );

    );
    

Cheers,

Jonas

Re: Loop to set a normalization formula for several columns

Hi Jonas,

 

thanks for your response. Small changes that led to sucess