topic Re: Select where and subset based on cell value in Discussions

Select where and subset based on cell value

Kriti — Fri, 09 Jun 2023 23:31:28 GMT

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] );
    );
);

Re: Select where and subset based on cell value

txnelson — Wed, 08 Jul 2020 22:20:11 GMT

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

Re: Select where and subset based on cell value

Kriti — Sun, 12 Jul 2020 19:07:33 GMT

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.