How to sort a table by Marked rows?

[RJain]

It should be a simple question but I don't seem to find answer for it.

I have a table and I am marking some rows in this table using a list. 
Once Marked, I want to sort the table , such that marked rows appear on top ( the table is sorted by marked rows)

 

 

[Kirsten Smith (she/her)]

There doesn't seem to be a way to handle this in the base Spotfire program, I would encourage you to vote for this suggestion in our Ideas Portal.

https://ideas.spotfire.com/ideas/SPF-I-4859

 

[Anthony Alvarez]

There might be a better way to do this, but one solution would be to monitor the marking event using a data function ("Marking Listener" in the attached dxp), track marked rows in a document property, and update a data table calculated column to sort by items marked.

• The data function in the attachment will update each time a marking changes. The output of the data function will store identifiers for each of the marked rows and will save the identifiers into a document property
• You can build a calculated column ("Check if in Marking List") in your data table to see which rows are marked
  • I used simple identifiers like: A, B, C, D, ... and used the Find() function to help me find the identifiers vs the marking list document property values. More complicated identifiers may see issues with using the Find() function to locate them. You may need to get more creative to search the marking list document property for items that are marked. Depending on your identifiers you may see false positive matches. Keep an eye on this.
• The last part would be to create a "Sort By" column that will be used to sort your table. I used a rank() function to rank items that were marked and any unmarked items did not get a rank.

Example:

Example 2:

Sort by Marking.dxp

[Vanessa Virginia Sucre Gonzalez]

Hi @RJain

I created an IronPython script to sort table visualisations with marked rows in first position with no need of an extra Sort By column.

The script first creates a Custom Sort Order (this will ONLY apply to String data columns). Once the Data column is sorted, you apply it directly to the visualisation (In this case, our data table, but you could use any type).

Here I leave the code and a dxp with the working example:

from System.Collections.Generic import List
from Spotfire.Dxp.Data import *
from System.Reflection import Assembly 
from Spotfire.Dxp.Data.Collections import *
from System.Runtime.Serialization import ISerializable
from System.Collections import IComparer
from System.Collections.Generic import IComparer
from Spotfire.Dxp.Application.Visuals import *

class MarkData:
	def __init__(self, value, mark):
		self.value = value
		self.mark = mark
	def __repr__(self):
		return repr((self.value, self.mark))

cursor = DataValueCursor.CreateFormatted(dataTable.Columns["ATA"])

markings = Document.ActiveMarkingSelectionReference.GetSelection(dataTable)

markedata = []

for row in dataTable.GetRows(markings.AsIndexSet(),cursor):
	value = cursor.CurrentValue
	markedata.append(MarkData(value, 1))


for row in dataTable.GetRows(markings.AsIndexSet().AsWritable().Not(),cursor):
	value = cursor.CurrentValue
	markedata.append(MarkData(value, 0))


sortedMarkedata = sorted(markedata, key=lambda value: -value.mark)


sortList = List[str]()

for item in sortedMarkedata:
	sortList.Add(item.value)
	print(item.value)

dataTable.Columns["ATA"].Properties.SetCustomSortOrder(sortList)


myVis = myVis.As[Visualization]()
dataTable = myVis.Data.DataTableReference

myVis.SortInfos.Clear()

myVis.SortInfos.Add(dataTable.Columns.Item["ATA"], TablePlotColumnSortMode.Ascending)

PARAMETERS:

• myVis -> Data Table Visualisation
• dataTable -> Your Data table with the Column you want to sort

Please let me know if this solution works for you :)
 

Sort By Marking.dxp