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: ClassStudentMarksSubject1A10 Math2A15Science3A12Science1B15Math2B12Science Output: ClassStudentSubjectmax(Marks)1AMath10 2AScience153AScience121BMath152BScience12 Example 2: (It has column "Class" but not column "subject") Input: ClassStudentMarks1A10 2A153A121B152B12 Output: ClassStudentmax(Marks)1A10 2A153A121B152B12 Example 3: (It has column "subject" but not column "class") Input: StudentMarksSubjectA10 MathA15ScienceA12ScienceB15MathB12Science Output: StudentSubjectmax(Marks)AMath10 AScience15BMath15BScience12 Please help me to resolve this problem. Thanks

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JMP Tabulate by list of column names in JSL

Rajat

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:

Class	Student	Marks	Subject
1	A	10	Math
2	A	15	Science
3	A	12	Science
1	B	15	Math
2	B	12	Science

Output:

Class	Student	Subject	max(Marks)
1	A	Math	10
2	A	Science	15
3	A	Science	12
1	B	Math	15
2	B	Science	12

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

Input:

Class	Student	Marks
1	A	10
2	A	15
3	A	12
1	B	15
2	B	12

Output:

Class	Student	max(Marks)
1	A	10
2	A	15
3	A	12
1	B	15
2	B	12

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

Input:

Student	Marks	Subject
A	10	Math
A	15	Science
A	12	Science
B	15	Math
B	12	Science

Output:

Student	Subject	max(Marks)
A	Math	10
A	Science	15
B	Math	15
B	Science	12

Please help me to resolve this problem.

Thanks

Accepted answers

txnelson

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.

All comments

txnelson

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

Rajat

@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

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?

Rajat

@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