cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
fr2007
Level II

searching column names and performing portion of code using JSL

hi all

i have a requirement where I need to search the column names of the .jmp file and then perform some steps only if that column is present. How do I include this column search in JSL? for example, i have a data in excel file with column names "A" ,"B","C","D".The column D has a filter and the options are "Yes" "No" and "May be" . Using JSL , I grouped the data sheet using Table->tabulate->option . I grouped the data in column "D".  Now I have a table with columns "yes,no and may be" . Now I want to search these columns for the option" may be" . If " may be" is available, i want to perform some steps. If not, I want to perform some other steps. how do I include this in JSL?

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: searching column names and performing portion of code using JSL

You're almost there.  You need to give everything a name so that you can refer to it later.  This code should work:

dt = Data Table( "Data Sheet" );

dttab = dt << Tabulate(

      Add Table(

      Column Table( Grouping Columns( :User Status Task ) ), //User Status Task has different status everytime and each has a value of 0 or 1. Each time depending on the data, some options can be missing.

      Row Table( Grouping Columns( :Serial Number, :Created by ) )

      )

);

newdt = dttab << Make Into Data Table;

name_list = {"APPR", "SEQU APPR", "SEQU HAPP", "HAPP", "RJCT", "SEQU RJCT", "SEQU HREJ", "HREJ", "DREQ", "SEQU DREQ"}; //I have these options under the drop down list column"User Status Task". I dont have the same drop downs everytime as data is missing in that column.

main_names = newdt << Get Column Names( String );

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

      If( Contains( main_names, name_list[i] ) == 0,

            newdt << New Column( name_list[i], Number, <<set each value( 1000 ) ); // I set each value as 1000 as I have 0 or 1 already for those options

      );

);

View solution in original post

6 REPLIES 6
pmroz
Super User

Re: searching column names and performing portion of code using JSL

I do this a lot because in our data you're not assured of all responses.  If a column is not there, I add it in and put 0s in it.  Something like this:

// Insert missing columns. 

name_list = {"Current", "Previous", "Cumulative"};

main_names = surv_pt_main << Get Column Names(String);

// If a column is missing, add it in with a set of 0s

for (i = 1, i <= nitems(name_list), i++,

    if (contains(main_names, name_list[i]) == 0,

        surv_pt_main << New Column(name_list[i], Number, << set each value(0));

    );

);

fr2007
Level II

Re: searching column names and performing portion of code using JSL

JSL is showing some error in surv_pt_main line.

I wrote the script as follows:

dt=data table("Data Sheet");

<<Tabulate(

Add Table(

  Column Table( Grouping Columns( :User Status Task ) ), //User Status Task has different status everytime and each has a value of 0 or 1. Each time depending on the data, some options can be missing.

  Row Table( Grouping Columns( :Serial Number,:Created by) )

))

<< Make Into Data Table;

name_list={"APPR","SEQU APPR","SEQU HAPP","HAPP","RJCT","SEQU RJCT","SEQU HREJ","HREJ","DREQ","SEQU DREQ"}; //I have these options under the drop down list column"User Status Task". I dont have the same drop downs everytime as data is missing in that column.

main_names=create_list<<Get Column Names(String);

for(i=0,i<=nitems(name_list),i++,

if(contains(main_names,name_list)==0,

surv_pt_main<<New Column(name_list,Number,<<set each value(1000)); // I set each value as 1000 as I have 0 or 1 already for those options

);

);

hope some one can help me with this.

thanks

ms
Super User (Alumni) ms
Super User (Alumni)

Re: searching column names and performing portion of code using JSL

For an outsider your script is quite confusing and it is impossible to say exactly what goes wrong without more info. Try to describe "some error" in some further detail.

A guess is that you adress the wrong table or that an inferred table does not exist. You appear to work with at least four data tables: the table called "Data Sheet" and the three table variables dt, create_list & surv_pt_main. Are you sure you have or use all these tables? For example, you make dt from a tabulate of "Data Sheet". But you never actually use dt?

pmroz
Super User

Re: searching column names and performing portion of code using JSL

You're almost there.  You need to give everything a name so that you can refer to it later.  This code should work:

dt = Data Table( "Data Sheet" );

dttab = dt << Tabulate(

      Add Table(

      Column Table( Grouping Columns( :User Status Task ) ), //User Status Task has different status everytime and each has a value of 0 or 1. Each time depending on the data, some options can be missing.

      Row Table( Grouping Columns( :Serial Number, :Created by ) )

      )

);

newdt = dttab << Make Into Data Table;

name_list = {"APPR", "SEQU APPR", "SEQU HAPP", "HAPP", "RJCT", "SEQU RJCT", "SEQU HREJ", "HREJ", "DREQ", "SEQU DREQ"}; //I have these options under the drop down list column"User Status Task". I dont have the same drop downs everytime as data is missing in that column.

main_names = newdt << Get Column Names( String );

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

      If( Contains( main_names, name_list[i] ) == 0,

            newdt << New Column( name_list[i], Number, <<set each value( 1000 ) ); // I set each value as 1000 as I have 0 or 1 already for those options

      );

);

fr2007
Level II

Re: searching column names and performing portion of code using JSL

yes that works . But I have something that is stopping me from executing the code.

Row Table( Grouping Columns( :Serial Number, :Created by ) ) 

      )

);

newdt = dttab << Make Into Data Table;

name_list = {"APPR", "SEQU APPR", "SEQU HAPP", "HAPP", "RJCT", "SEQU RJCT", "SEQU HREJ", "HREJ", "DREQ", "SEQU DREQ"}; // In these options, I want to get the rows that have values' 0' for DREQ and SEQU DREQ columns. These two columns can hold values of either 1 or 0 but I dont want rows with values 0.

main_names = newdt << Get Column Names( String );

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

      If( Contains( main_names, name_list[i] ) == 0,

            newdt << New Column( name_list[i], Number, <<set each value( 1000 ) ); // I set each value as 1000 as I have 0 or 1 already for those options

      );

);

In the above code, whenever I have the columns DREQ and SEQU DREQ, I want all the rows that have the values of 0 in either of these columns. If these columns are not present then the value 1000 is being displayed in the code. But How do I incorporate this condition that I need only 0 if these columns are present. hope you understood my question.

Thanks a lot

euge
Level III

Re: searching column names and performing portion of code using JSL

very helpful solution