How to select or filter the latest date or current date in Listboxfilter with reverse sort order through Iron python script in Spotfire 7.8

[Maddy Keyan]

I am looking for a ironpython script/any expression for reverse sort order for date column + it has to select recent/latest date for the same date column in the listbox filter of text area viz

 

Please share any script/expression if you find any.

 

Thanks

Maddy

[Shandilya Peddi]

The following script reads the values in a column and then sets the last value in the column to a document proeprty. See if this helps

from System import Array

from Spotfire.Dxp.Data import IndexSet

from Spotfire.Dxp.Data import DataValueCursor

import datetime

# Get table

tbl= Document.Data.Tables["Data Table"]

 

# Get access to the Column populating the choice values

# of the drop-down list control

choicesCol = tbl.Columns["Column 1"]

 

rowCount = tbl.RowCount

rowsToInclude = IndexSet(rowCount,True)

 

# Create a cursor to the Column we wish to get the values from

cursor1 = DataValueCursor.CreateFormatted(choicesCol)

 

# Create Array object that will hold values

strArray = Array.CreateInstance(str,rowCount)

 

# Loop through all rows, retrieve value for specific column,

# and add value into array

for row in tbl.GetRows(rowsToInclude,cursor1):

rowIndex = row.Index

value1 = cursor1.CurrentValue

strArray[rowIndex-1] = value1

 

# Get only unique Values, cast to a list

uniqueVal = list(set(strArray))

 

 

#sorted values

uniqueVal.sort(key = lambda date: datetime.datetime.strptime(date,"%m/%d/%Y"),reverse=True)

 

# Set document property to first value found in the list

Document.Properties["test"] = uniqueVal[0]

[Maddy Keyan]

Peddi - Thanks for your response. I truly appreciate your way of approach would give a try to implement above given script. However, i was wondering if we could tweak little bit with the below script (got date reverse column sorted) with any minor changes as a result we could also get "select recent/latest date" from the same column filter.here's the script below:from System.Reflection import Assemblyfrom Spotfire.Dxp.Data.Collections import *from System.Runtime.Serialization import ISerializablefrom System.Collections import IComparerfrom System.Collections.Generic import IComparercolumn = Document.Data.Tables['Table_Name'].Columns['Column_Name']values = column.RowValues.GetEnumerator()myValues = []for val in values:    if val.HasValidValue:  #exclude empty values        myValues.Add(val.ValidValue)myValues.sort(reverse=True)column.Properties.SetCustomSortOrder(myValues)

 

[Shandilya Peddi]

In this script you shared, myValues[0] should give you the recent/last date available in the colum

 

print myValues[0]

 

 

[Maddy Keyan]

peddi - When i add latest date value within myValues=[10/28/2019] it throws error when i runscript as "ValueError: Illegal type for value".

 

Is there anyother alternative

[Shandilya Peddi]

Do you see the same issue if you do the below

 

myValues=["10/28/2019"]

 

 

[Maddy Keyan]

Peddi - I was able to execute"print myValues[0]" however, latest date/current date does not get picked up. And instead to set document. properties in the end is it possible to do through Column.Properties["test"] = uniqueVal[0]

[Maddy Keyan]

Peddi - I was able to execute"print myValues[0]" however, latest date/current date does not get picked up. And instead to set document. properties in the end is it possible to do through Column.Properties["test"] = uniqueVal[0]

[Shandilya Peddi]

For setting a document property it is as follow

 

Document.Properties["test"] = uniqueVal[0]

 

 

[Maddy Keyan]

Peddi - Sure,  upon adding Document.Properties["test"] = uniqueVal[0] worked fine I did try but the script doesnt let me select latest date in the list box filter. Are they any other alternatives

[Maddy Keyan]

Peddi - The script worked really fine i was very close to my result but the issue would be addition of one more filter which makes crowded in text area. I was wondering is there any other option where we can embed the same script within already existing filter of text area

[Maddy Keyan]

Peddi - I have found the answer to my question hope this helps for other users as well if they ever come across such scenario. Please find the below Ironpython script for reverse sort date order(descending order)that has to select latest date in listbox filter.

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

 

import Spotfire.Dxp.Application.Filters as filters

 

import Spotfire.Dxp.Application.Filters.ListBoxFilter

 

from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers

 

column = Document.Data.Tables['TABLE_NAME'].Columns['COLUMN_NAME']

 

values = column.RowValues.GetEnumerator()

 

#Val is a value inside the Column specified in the GetFilter

 

#print values

myValues = []

 

for Val in values:

 

if Val.HasValidValue: #exclude empty values

myValues.Add(Val.ValidValue)

print Val.ValidValue

 

myValues.sort(reverse=True)

 

column.Properties.SetCustomSortOrder(myValues)

 

myPanel = Document.ActivePageReference.FilterPanel

myFilter= myPanel.TableGroups[0].GetFilter("COLUMN_NAME")

 

#ListBoxFilter

#myFilter.FilterReference.TypeId = FilterTypeIdentifiers.ListBoxFilter

listBoxFilter = myFilter.FilterReference.As[filters.ListBoxFilter]()

 

listBoxFilter.IncludeAllValues=False

listBoxFilter.SetSelection(myValues[0])

 

#uncheck all

#ListBoxFilter.IncludeEmpty = False #make sure to clear the empty values

#for value in ListBoxFilter.Values:

# ListBoxFilter.Uncheck(value)

 

#ListBoxFilter.Check(myValues)