.gif)
* You have an input field in a text area (colname document property)
* You can pass multiple values (space separated) to the filter from the text area input field.
* You want to take the value from this text field and pass the value to a filter named 'Site Name'
* The filter in the filter palnel is a ListBoxFilter
import Spotfire.Dxp.Application.Filters as filters
import Spotfire.Dxp.Application.Filters.ListBoxFilter
from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers
from Spotfire.Dxp.Data import DataPropertyClass
from System import String
myPanel = Document.ActivePageReference.FilterPanel
myFilter= myPanel.TableGroups[0].GetFilter("Site Name")
lbFilter = myFilter.FilterReference.As[filters.ListBoxFilter]()
lbFilter.IncludeAllValues=False
strVals = Document.Properties["colname"]
if strVals!=String.Empty:
lbFilter.SetSelection([v.strip() for v in strVals.split(",")])
else:
lbFilter.Reset()
Example with Range Filter
from Spotfire.Dxp.Application.Filters import RangeFilter, ValueRange
lower and upper values
dt = Document.Data.Tables["DataTable"]
a,b = 10000, 20000
col="Weight"
#get filter reference and reset filter
filt=Document.FilteringSchemes[0].Item[dt].Item[dt.Columns.Item[col]].As[RangeFilter]()
filt.Reset()
#find lower and upper bounds from column
ls,us=set(),set()
rows = Document.ActiveFilteringSelectionReference.GetSelection(dt).AsIndexSet()
for r in rows:
v=dt.Columns[col].RowValues.GetValue(r).Value
if v>=a:ls.add(v)
if v<=b:us.add(v)
#set value range on filter
print min(ls),max(us)
filt.ValueRange = ValueRange(min(ls),max(us))
16 comments:
How do i modify this script to use comma(,) as a separator instead of space?
Hello Sql Sean,
Just pass the spearator you are using, in this case, a coma, as your argument to the split function.
The split() function is converting the colname string to list of items a,b and c. The default separator is a space. If your original colname string is spearated by comas (e.g. "a,b,c"), then do:
...strVals.split(","))
Hello Jose,
Can you provide te code if I have to make use of two list box filters instetad of a text filter and list box filter. I mean depending upon the selections made in the first list box filters, same should be filtered out in the second list box.
Thanks
Bharath
Hello Bharath,
Check out this post
Hope this helps.
This does not function if a value from the list pf specified values doe not exist in the drop down. Is there a way to resolve this? Appreciate your help.
Hello VD, you can select unique values from the filter into a list and do an intersection between the strVals from the doc prop and feed your setSelection with it:
# Select unique values from the filter into a list
# dt is a script param to your talbe as well as columnName (string)
cursor = DataValueCursor.Create[str](dt.Columns[columnName])
distinctRows = dt.GetDistinctRows(None,cursor)
c1 = []
distinctRows.Reset()
while distinctRows.MoveNext():c1.append(cursor.CurrentValue)
# Do an intersection between the strVals
# splits "a, b, c" by comma and remove spaces)
c2 = map(str.strip,strVals.split(","))
c3 = c1 & c2
# Feed your setSelection with it
if c3.Count>0:
lbFilter.SetSelection(c3)
else:
lbFilter.Reset()
Hi Jose,
How it is possible to reset property control in text area
Thanks,
What if we have multiple data tables with same column name?
Hello Junaid,
Just change the TableGroups index number (TableGroups[0])
myFilter= myPanel.TableGroups[1].GetFilter("Site Name")
Hi Jose,
How do I modify the script if the property control and column values are integers instead of string?
Hello Unknown, that is a little trickier if you are using a Range filter. I updated the post. Hope this helps!
Hi Jose,
I tried the script in "Example with Range Filter" but it seems to be slow when used on a 600.000 rows dataset. Do you have any suggestions on how to improve its performance?
Thank you for your help!
Edoardo
Hello Edoardo, Maybe by removing the loop to find min and max values. There might be a way to directly get these limits faster even without looping through the API. Let us know if you found a way
Hello Jose, thank you for your reply!
That was my first try! I tried to convert the input value (for max/min value) to Decimal type but it didn't seem to work :(
From what you recall, should I have to pass a current value of the column for max/min value?
I will try to explain better, if the user wants to filter data in the column < 1000, can I use that number even though it's not a value of the column? or do I need to use the number in the column closer to 1000 (like you did in that loop)?
Edoardo
If your range filter is categorical yes, you need the loop or enter the exact value. If it is continuous, you can enter any value. Try changing the column data type to a Real.
# Filter is continious
# CONT_COL={1,2,3,8,9}
from Spotfire.Dxp.Application.Filters import RangeFilter, ValueRange
filt=Document.FilteringSchemes[0].Item[dt].Item[dt.Columns.Item["CONT_COL"]].As[RangeFilter]()
filt.Reset()
filt.ValueRange = ValueRange(4,8)#◄ works
# Filter is categorical
# CAT_COL={"1","2","3","8","9"}
from Spotfire.Dxp.Application.Filters import RangeFilter, ValueRange
filt=Document.FilteringSchemes[0].Item[dt].Item[dt.Columns.Item["CAT_COL"]].As[RangeFilter]()
filt.Reset()
filt.ValueRange = ValueRange("1","8")#◄ works because requires exact values
filt.ValueRange = ValueRange("4","8")#◄ wont work. "4" not in cat_col
Finally, I understood what I was doing wrong!
I had to convert the column data type from currency to real. For currency data type I had to use a Decimal type as max/min value in the ironpython script but I didn't find a way to make it works.
While converting to datatype "real", I used float type as max/min and it works smoothly :)
Thank you again!
Post a Comment