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

searching column names and performing portion of code using JSL

fr2007 — Fri, 21 Sep 2012 16:29:04 GMT

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?

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

pmroz — Fri, 21 Sep 2012 18:17:13 GMT

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

);

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

fr2007 — Mon, 24 Sep 2012 00:24:34 GMT

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

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

ms — Mon, 24 Sep 2012 11:03:38 GMT

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?

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

pmroz — Mon, 24 Sep 2012 13:10:45 GMT

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

);

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

fr2007 — Mon, 24 Sep 2012 14:46:14 GMT

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

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

euge — Tue, 27 Nov 2012 22:25:45 GMT

very helpful solution