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
Kriti
Level I

Select where and subset based on cell value

Hi,

 

I have the below piece of code where I want it to get column names from the file and then create a new subset table followed by finding mean and SD of all numeric columns. The Mean and SD code works but it is not creating the subset table at all. Any ideas? I have checked this against all posted discussions. I have tried both Starts with and Contains conditions

Here is the sample file:

Serial Num Code Area Height Weight Age
1 55414 Minneapolis N      
2 55455 Minneapolis S      
3 4446804e Minneapolis E      
4 4389568 Minneapolis W    
5 g58934 St. Paul N      
6 4989034 St. Paul E      
7 3496f St. Paul S      
8 5404390sfd Minneapolis E      
9 43960 Minneapolis N      
10 569043 St. Paul N      
11 6590549 St. Paul E      

 

 

dt3 = Open( "file_28.jmp" );
colcmp = "Area";
col = dt3 << get column names( string );
nc = N Items( col );
Show( nc );


For( j = 1, j <= nc, j++,
    colname = Column( j ) << Get Name;
    Show( colname );
    If( Contains( colname, colcmp ),
        dt3 << Select Where( Starts With( :colname, "Minneapolis" ) );
        dt3 << Subset( Output Table Name( "MPLS" ), Selected Rows( 1 ), Selected columns only( 0 ) );
        dt3 << Select Where( Starts With( :colname, "St. Paul" ) );
        dt3 << Subset( Output Table Name( "STP" ), Selected Rows( 1 ), Selected columns only( 0 ) );
    );
);

dtPM = Data Table( "MPLS" );

a = "Numeric";
colPM = dtPM << get column names( string );
ncPM = N Items( colPM );
Show( ncPM );

For( k = 1, k <= ncPM, k++,
    colname = Column( k ) << Get Name;
    Show( colname );
    which = dtPM:colname << Get Data Type;
    Show( which );
    If( Contains( which, a ),
        count = count + 1;
        colnames2[count] = colname || "_MPLS";
        Mean1[count] = Col Mean( :Column( k ) );
        Std1[count] = Col Std Dev( :Column( k ) );
        Show( Mean1[count] );
    );
);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Select where and subset based on cell value

You are telling JMP to find a column named :colname.   In this reference, JMP will not evaluate the column reference beyond :colname.  What you need to do, is to specify the name in an As Column() function.  Then it will work

For( j = 3, j <= nc, j++,
	colname = Column( j ) << Get Name;
	Show( colname );
	If( Contains( colname, colcmp ),
		dt3 << Select Where( Starts With( as column(colname), "Minneapolis" ) );
		dt3 << Subset( Output Table Name( "MPLS" ), Selected Rows( 1 ), Selected columns only( 0 ) );
		dt3 << Select Where( Starts With( as column(colname), "St. Paul" ) );
		dt3 << Subset( Output Table Name( "STP" ), Selected Rows( 1 ), Selected columns only( 0 ) );
	);
);

 

Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Select where and subset based on cell value

You are telling JMP to find a column named :colname.   In this reference, JMP will not evaluate the column reference beyond :colname.  What you need to do, is to specify the name in an As Column() function.  Then it will work

For( j = 3, j <= nc, j++,
	colname = Column( j ) << Get Name;
	Show( colname );
	If( Contains( colname, colcmp ),
		dt3 << Select Where( Starts With( as column(colname), "Minneapolis" ) );
		dt3 << Subset( Output Table Name( "MPLS" ), Selected Rows( 1 ), Selected columns only( 0 ) );
		dt3 << Select Where( Starts With( as column(colname), "St. Paul" ) );
		dt3 << Subset( Output Table Name( "STP" ), Selected Rows( 1 ), Selected columns only( 0 ) );
	);
);

 

Jim

View solution in original post

Highlighted
Kriti
Level I

Re: Select where and subset based on cell value

Thank you Jim. I was having confusion on how to pass variables that store column names to functions and could'nt really find examples. This cleared the error. 

Article Labels