I have the pie chart & need to create crosstable based on marking but it should connect marking with only one column & other columns in crosstable should show all the data from main table regardless of marking.
Attached image is better explanation of my question, please let me know if anyone can help.
Currently i am getting table like top right but I need cross table like bottom right. Question is explained in Excel format but I am solving this problem in spotfire
I tried using subsets , didn't work. Kind of stuck after that
The question, as I understand it, is how to extend the marking from students to their teachers.
Firstly, in order not to have to send the entire data table to the TERR script, define a calculated column [student_teachers] as:
UniqueConcatenate([teacher]) over ([student])
This will give you the list of teachers associated to every student.
Then you would create a new TERR data function, called e.g. markTeachers. The only input would be the data table with your students and teachers. You only need to send in the student_teachers column.
This table needs to be limited by the marking you are using to select your students. So the only data that enters the TERR script is the marked rows.
The TERR script is like this:
#Parameters:
#data the input data table, restricted to marked rows
if (nrow(data)>0) {
tmp=paste(unique(data$student_teachers),collapse=', ')
tmp=unique(strsplit(tmp,', ')[[1]])
markedTeachers=paste(paste0('$',tmp,'$'),collapse=',')
} else {
markedTeachers=''
}
The variable markedTeachers outputs to a document property, say you call it also markedTeachers.
There are a few gyrations that might look odd:
1 - you might have selected more than one student. Therefore the variable tmp is first filled with the concatenation of all marked students, then trimmed and turned back into a vector, containing the unique teachers' list.
2 - there might be teacher names that are one the substring of another. If you don't put some markers to isolate a complete name, you might collect more teachers than you wanted. That is why I surrounded every teacher's name with $.
You can set this script to run automatically every time you change the marking.
In your cross table, you would use the following Limit data using expression:
Find(Concatenate('$',[teacher],'$'),'${markedTeachers}')>0
Again, I surround the teacher by $ so you don't accidentally find substrings of their names.