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
Rajat
Level IV

JMP Tabulate by list of column names in JSL

Hello,

 

I have table and I want to tabulate it by column "student", column "subject" and column "class" but column "Subject" and column "class" is not necessary to available in the data.

If it has column "subject" but not column "class" then it should tabulate by column "student" and column "subject".

If it has column "class" but not column "subject" then it should tabulate by column "student" and column "class".

If it has all the columns then it should tabulate by all three columns. Below are some examples.

 

Example 1: (It has both column "subject" and column "class")

Input:

ClassStudentMarksSubject
1A

10

Math
2A15Science
3A12Science
1B15Math
2B12Science

 

Output:

ClassStudentSubjectmax(Marks)
1AMath

10

2AScience15
3AScience12
1BMath15
2BScience12

 

Example 2: (It has column "Class" but not column "subject")

Input:

ClassStudentMarks
1A

10

2A15
3A12
1B15
2B12

 

Output:

ClassStudentmax(Marks)
1A

10

2A15
3A12
1B15
2B12

 

Example 3: (It has column "subject" but not column "class")

Input:

StudentMarksSubject
A

10

Math
A15Science
A12Science
B15Math
B12Science

 

Output:

StudentSubjectmax(Marks)
AMath

10

AScience15
BMath15
BScience12

 

Please help me to resolve this problem.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: JMP Tabulate by list of column names in JSL

Is this what you are looking for???

names default to here(1);
dt=current data table();

// The list of columns to look for
theList={"Class","Student","Subject", "xxxxxx","xxxxxx"};

allColList = dt << get column names(string);

// Remove any columns from theList that are not in the data table
For(i=n items(theList),i>=1, i--,
	If(n rows(loc(allColList,theList[i]))==0,
		remove from(theList,i,1)
	)
);

// Run the tabulate
Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Analysis Columns( :Marks ), Statistics( Max ) ),
		Row Table( Grouping Columns( eval(theList) ) )
	)
);

If the different target columns play roles other than Grouping Columns, you will need to develop the code accounting for the different required input syntax.

Jim

View solution in original post

6 REPLIES 6
txnelson
Super User

Re: JMP Tabulate by list of column names in JSL

Run each of the 3 scenarios on a sample data table, saving each of the scripts from each run, into a script window.  Then take each of the appropriate scripts and put them into the following script in the designated position

names default to here(1);
dt= current data table();

colNames = dt << get column names(string);

// Here is the IF() structure to add in the tabulate code to
If( N Rows(loc(colNames, "Subject" ) ) > 0 & N Rows(loc(colNames, "Class" ) ) > 0,
// Add in the tabulate code that has Student, Subject and Class


,
 N Rows(loc(colNames, "Subject" ) ) > 0,
 // Add in the tablulate code that has just Student and Subject
 
 
 ,
 N Rows(loc(colNames, "Class" ) ) > 0,
 // Add in the tablulate code that has just Student and Class
 
 );
Jim
Rajat
Level IV

Re: JMP Tabulate by list of column names in JSL

@txnelson Thanks for replying.

Can we solve this problem with single block of tabulate script because in my original dataset I have many such columns.
txnelson
Super User

Re: JMP Tabulate by list of column names in JSL

Condensing the Tabulate code down to one set of code, would make the code far more complex.

 

When you say you have many more columns, can you explane further?

Jim
Rajat
Level IV

Re: JMP Tabulate by list of column names in JSL

@txnelson I have 5 column like Column "Class" and Column "Subject" which can be present in the data or not. Now to tabulate I have to give all permutation of IF conditions and each would have separate tabulate code. It will make code will become too messy.
Instead if we can do it with single Tabulate function in which we give all the list of columns and it will take only those columns which are available in the data.

Thanks
txnelson
Super User

Re: JMP Tabulate by list of column names in JSL

Is this what you are looking for???

names default to here(1);
dt=current data table();

// The list of columns to look for
theList={"Class","Student","Subject", "xxxxxx","xxxxxx"};

allColList = dt << get column names(string);

// Remove any columns from theList that are not in the data table
For(i=n items(theList),i>=1, i--,
	If(n rows(loc(allColList,theList[i]))==0,
		remove from(theList,i,1)
	)
);

// Run the tabulate
Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Analysis Columns( :Marks ), Statistics( Max ) ),
		Row Table( Grouping Columns( eval(theList) ) )
	)
);

If the different target columns play roles other than Grouping Columns, you will need to develop the code accounting for the different required input syntax.

Jim
Rajat
Level IV

Re: JMP Tabulate by list of column names in JSL

Thanks You @tsnelson