Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
viskovicz00012
Level II

Loop

Manual :

1.  I have 2 data tables ,one is the parameter list with control limits and the other is the main data table.

 
 

Screenshot_7.pngScreenshot_9.png

 

2. I have to sort each column manually [ascending order] based on the order in the template. If Contact is less than 2 [DC is 6 ] or Contact greater than 10 [DC is 7] and put it in the DC column -- After I input values in DC, i hide and exclude  the rows with DC values and proceed to the next parameter and sort it again

 

How do i do it for loop if i have several parameters and a big data set?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Loop

First, I believe, that given the sample data tables you provided, that you made a mistake in your verbiage when talking about

"Contact greater than 10 [DC is 7]".  Given the values in the Parameter data table, I believe you meant to write:

"Contact greater than 23 [DC is 7]"

Assuming this, I believe the below script will work for what you want.  It does not require going through row by row in the data table, which means it does not have to be sorted.  I hope it is what you need

Names Default To Here( 1 );

// Create Sample data tables
dtParms = New Table("Parameters",
	Add Rows(4),
	New Column("Order",formula(Row())),
	New Column("Parameter", character,values({"Contact","MST","Height","Weight"})),
	New Column("CL_LL", values([2,23,59,35])),
	New Column("CL_UL", values([23,100,109,105])),
	New Column("DC_LL", values([6,10,21,33])),
	New Column("DC_UL", values([7,11,22,39]))
);

dtMain = New Table("Main",
	Add Rows(100),
	New Column("Contact", formula(randominteger(1,27))),
	New Column("MST", formula(randominteger(18,115))),
	New Column("Height", formula(randominteger(50,120))),
	New Column("Weight", formula(randominteger(30,110))),
	New Column("DC")
);
dtMain << run formulas;
dtMain:Contact << delete formula;
dtMain:MST << delete formula;
dtMain:Height << delete formula;
dtMain:Weight << delete formula;

// Loop through the data and assign the above and below limits
For(i=1,i<=N Rows(dtParms),i++,
	foundRows = dtMain << get rows where( as column(dtMain, dtParms:Parameter[i]) < dtParms:CL_LL[i] &  isMissing(dtMain:DC) == 1 );
	dtMain:DC[foundRows] = dtParms:DC_LL[i];
	foundRows = dtMain << get rows where( as column(dtMain, dtParms:Parameter[i]) > dtParms:CL_UL[i] & isMissing(dtMain:DC) == 1);
	dtMain:DC[foundRows] = dtParms:DC_UL[i];
);
Jim

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

Re: Loop

First, I believe, that given the sample data tables you provided, that you made a mistake in your verbiage when talking about

"Contact greater than 10 [DC is 7]".  Given the values in the Parameter data table, I believe you meant to write:

"Contact greater than 23 [DC is 7]"

Assuming this, I believe the below script will work for what you want.  It does not require going through row by row in the data table, which means it does not have to be sorted.  I hope it is what you need

Names Default To Here( 1 );

// Create Sample data tables
dtParms = New Table("Parameters",
	Add Rows(4),
	New Column("Order",formula(Row())),
	New Column("Parameter", character,values({"Contact","MST","Height","Weight"})),
	New Column("CL_LL", values([2,23,59,35])),
	New Column("CL_UL", values([23,100,109,105])),
	New Column("DC_LL", values([6,10,21,33])),
	New Column("DC_UL", values([7,11,22,39]))
);

dtMain = New Table("Main",
	Add Rows(100),
	New Column("Contact", formula(randominteger(1,27))),
	New Column("MST", formula(randominteger(18,115))),
	New Column("Height", formula(randominteger(50,120))),
	New Column("Weight", formula(randominteger(30,110))),
	New Column("DC")
);
dtMain << run formulas;
dtMain:Contact << delete formula;
dtMain:MST << delete formula;
dtMain:Height << delete formula;
dtMain:Weight << delete formula;

// Loop through the data and assign the above and below limits
For(i=1,i<=N Rows(dtParms),i++,
	foundRows = dtMain << get rows where( as column(dtMain, dtParms:Parameter[i]) < dtParms:CL_LL[i] &  isMissing(dtMain:DC) == 1 );
	dtMain:DC[foundRows] = dtParms:DC_LL[i];
	foundRows = dtMain << get rows where( as column(dtMain, dtParms:Parameter[i]) > dtParms:CL_UL[i] & isMissing(dtMain:DC) == 1);
	dtMain:DC[foundRows] = dtParms:DC_UL[i];
);
Jim

View solution in original post

Highlighted
viskovicz00012
Level II

Re: Loop

Follow up question:

Instead of having to code the dtParms, i stored them into a data table with the same name but it does not produce results with the one using scripting -- it does however says "Scoped data table access requires a data table column or variable{1} " - how do i address this? 

Highlighted
txnelson
Super User

Re: Loop

For the code:

For(i=1,i<=N Rows(dtParms),i++,
	foundRows = dtMain << get rows where( as column(dtMain, dtParms:Parameter[i]) < dtParms:CL_LL[i] &  isMissing(dtMain:DC) == 1 );
	dtMain:DC[foundRows] = dtParms:DC_LL[i];
	foundRows = dtMain << get rows where( as column(dtMain, dtParms:Parameter[i]) > dtParms:CL_UL[i] & isMissing(dtMain:DC) == 1);
	dtMain:DC[foundRows] = dtParms:DC_UL[i];
);

 to work right, you need to associate the variable "dtParms" with the data table that contains the Parameters.  You stated you saved the data table with the same name, so I am assuming you named the data table "Parameters".  So what you need to do, is to open up that data table before running the script, and to add to the script the following statement

dtParms = Data Table("Parameters");
Jim
Article Labels

    There are no labels assigned to this post.