308433_en-US

liliconcom

Iterate script over different table

I have this defecting level which determines the number of times it will iterate.

 

dtparms << Subset( By( :Defecting_Level ), All rows, Selected columns only( 0 ), columns( :Parameter, :CL_LL, :CL_UL, :MD_LL, :MD_UL ) );
  
dtParms1 = Data Table( "Defecting_Level=1" );<BR />dtParms2 = Data Table( "Defecting_Level=2" );

 

This is the code that needs to iterate depending on how many defecting levels there are. My problem is can I iterate through different tables using the same script? Or how can I repeat this process depending on the number of defecting levels? Like for example, my defecting level is 3. Then this code needs to run three times, iterating through the three tables that has been subset. Thanks

deftb << New Column( "MD1", numeric );
parameters = Column( dtParms1, "Parameter" ) << get values; 
deftb_colnames = deftb << get column names( string ); 
parameters = Associative Array( Eval( parameters ) ); 
deftb_colnames = Associative Array( Eval( deftb_colnames ) ); 
parameters << Intersect( deftb_colnames ); 
params = parameters << get keys; 

For( i = 1, i <= N Items( params ), i++,
 foundRows = deftb << get rows where( As Column( deftb, dtParms1:Parameter[i] ) < dtParms1:CL_LL[i] & Is Missing( deftb:MD1 ) == 1 );
 deftb:MD1[foundRows] = dtParms1:MD_LL[i];
 foundRows = deftb << get rows where( As Column( deftb, dtParms1:Parameter[i] ) > dtParms1:CL_UL[i] & Is Missing( deftb:MD1 ) == 1 );
 deftb:MD1[foundRows] = dtParms1:MD_UL[i];
); 

 

AnalyticsData TableOtherScriptingRe: Iterate script over different table

I am sure, that the JSL below, as written, will not work.  The sample code that was provided had pointers to data tables not defined, so therefore, I am making some assumptions that may not be exactly correct.  However, if you follow the logic and syntax I provide, you should be able to get an idea of how to have your one piece of code used to loop across whatever number of input parameters specified

// By specifying the variable to hold the data table names
// that will be created from the Subset with the By clause
// the variable specified becomes a List with an element
// for each table created
theTables = dtparms << Subset(
 By( :Defecting_Level ),
 All rows,
 columns( :Parameter, :CL_LL, :CL_UL, :MD_LL, :MD_UL )
);

Show( theTables );

For( k = 1, k <= N Items( theTables ), k++, 

 deftb << New Column( "MD1", numeric );
 parameters = Column( theTables[k], "Parameter" ) << get values;
 deftb_colnames = deftb << get column names( string );
 parameters = Associative Array( Eval( parameters ) );
 deftb_colnames = Associative Array( Eval( deftb_colnames ) );
 parameters << Intersect( deftb_colnames );
 params = parameters << get keys; 

 For( i = 1, i <= N Items( params ), i++,
  foundRows = deftb << get rows where(
   As Column( deftb, column( theTables[k],"Parameter")[i] ) < column( theTables[k], "CL_LL")[i] &
   Is Missing( deftb:MD1 ) == 1
  );
  deftb:MD1[foundRows] = column(theTables[k],"MD_LL")[i];
  foundRows = deftb << get rows where(
   As Column( deftb, column( theTables[k],"Parameter")[i] ) < column( theTables[k], "CL_UL")[i] &
   Is Missing( deftb:MD1 ) == 1
  );
  deftb:MD1[foundRows] = column( theTables[k],"MD_UL")[i];
 );
);

Re: Iterate script over different table

Thank you for a quick answer! I have a follow up question tho,
deftb << New Column( "MD1", numeric ); => which will be the storage of the iterated values, should be (MD1, MD2, MD3..) according to the number of iterations. For example, iteration for table 1 values will be stored in MD1, iteration for table 2 values will be stored in MD2 and so on. I tried adding this line but it doesn't work:
MD = {"MD1", "MD2", "MD3", "MD4", "MD5", "MD6"};
For( k = 1, k <= N Items( theTables ), k++,
deftb << New Column( MD[k + 1], numeric );

Thanks for the help

Re: Iterate script over different table

Both of the below pieces of code work great for me, as long as the deftb data table reference has been set, and the theTables list has been populated.  Since the script did not work for you, what error messages were there in the log?

MD = {"MD1", "MD2", "MD3", "MD4", "MD5", "MD6"};
For( k = 1, k <= N Items( theTables ), k++,
 deftb << New Column( MD[k + 1], numeric )
);

// or, why go through all of the work to create the list MD
// when you can easily create the character string required
// for the column name as shown below

deftb = Current Data Table();
For( k = 1, k <= N Items( theTables ), k++,
 deftb << New Column( "MD" || char[k + 1]), numeric )
);

Re: Iterate script over different tableNow it's working, thanks for the swift explanation. And last question, after creating the column storage for the values, this loop shows an error: Subscript Range{1} in access or evaluation of 'theTables[k]' , theTables[/*###*/k]

For( i = 1, i <= N Items( params ), i++,
foundRows = deftb << get rows where(
As Column( deftb, column( theTables[k],"Parameter")[i] ) < column( theTables[k], "CL_LL")[i] &
Is Missing( deftb:MD[k + 1] ) == 1
);
deftb:MD[k + 1][foundRows] = column(theTables[k],"MD_LL")[i];
foundRows = deftb << get rows where(
As Column( deftb, column( theTables[k],"Parameter")[i] ) < column( theTables[k], "CL_UL")[i] &
Is Missing( deftb:MD[k + 1] ) == 1
);
deftb:MD[k + 1][foundRows] = column( theTables[k],"MD_UL")[i];
);

Is it because of my MD[k+1]?Re: Iterate script over different table

To figure out what is going on here, all you need to do, is to determine the value of the variable k and it will tell you why referencing theTables[k] is pointing to an invalid subscript.  Typically this is because you have either used a For() loop with k as the index value, and in the specification of the For() loop, you are incrementing k beyond the number of elements of theTables list.  Or, the variable k what used in a previous part of the script, and when it is referenced at this point, the value of k is inappropriate.

 

If you hover over any of the references to the variable k in your script, it will display what the current value of k is.  You should be able to then find out where the value came from.

 

one last note....since you are not initializing your variables at the beginning of your script, the values of the variables will be set to the values they were at the last time the script ended.  Therefore, that can be causing the issue too.

Re: Iterate script over different table

So I'm kinda lost again with these loops, and I'm only getting the value of 6 inside MD1 column only instead of the values from each table from theTables and placed inside their respective MDs. SOrry I'm really confused with the For loop in jmp

theTables = dtparms << Subset(
    By( :Dispo_name ),
    All rows,
    Selected columns only( 0 ),
    columns( :Parameter, :CL_LL, :CL_UL, :MD_LL, :MD_UL )
);

//create columns
For( n = 1, n <= N Items( theTables ), n++,
    deftb << New Column( "MD" || Char( n ), numeric )
);

//loop through each table and store value in MDs
For( k = 1, k <= N Items( theTables ), k++,
    parameters = Column( theTables[k], "Parameter" ) << get values;
    deftb_colnames = deftb << get column names( string );
    parameters = Associative Array( Eval( parameters ) );
    deftb_colnames = Associative Array( Eval( deftb_colnames ) );
    parameters << Intersect( deftb_colnames );
    params = parameters << get keys;
    Show( params );

    For( i = 1, i <= N Items( params ), i++,
        foundRows = deftb << get rows where(
            As Column( deftb, Column( theTables[k], "Parameter" )[i] ) < Column( theTables[k], "CL_LL" )[i] &
            Is Missing( deftb:MD ) == 1
        );
        deftb:MD[foundRows] = Column( theTables[k], "MD_LL" )[i];

        foundRows = deftb << get rows where(
            As Column( deftb, Column( theTables[k], "Parameter" )[i] ) < Column( theTables[k], "CL_UL" )[i] &
            Is Missing( deftb:MD ) == 1
        );
        deftb:MD[foundRows] = Column( theTables[k], "MD_UL" )[i];
    );
);

Re: Iterate script over different table1. Please attach a sample data table so I can test the code against real data.
2. Please provide a mockup of what you expect the final data table to look like.