Filtering two tables without creating a relationship -- is it possible

[K Rojas]

Hello,

I have two data tables with matching columns. The manner in which the data is formatted prohibits creating a relationship, as this enforces an erroneous parity between the tables.

Ideally, I would be able to use both data tables in a vizualization and be able to filter both of them without a fixed relationship. I've looked a property controls (where one would control both tables), but I haven'e found anything specifcally helpful.

All ideas welcome, as I am at the end of my research rope.

Thanks,

[Tyger Guzman 2]

You can use a python script if the column names match. I used the following script to filter from the multi-list select box to filter multiple tables {the tables must have the same column name}

 

from Spotfire.Dxp.Data import *

import Spotfire.Dxp.Application.Filters as filters

from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers

from System import String

 

def ChangeFilterstoListBox():

#CHANGE ALL FILTERS TO LISTS BOX

for aPage in Document.Pages:

aFilterPanel = aPage.FilterPanel

for aTableGroup in aFilterPanel.TableGroups:

for aFilterHandle in aTableGroup.FilterHandles:

if aFilterHandle.FilterReference.Name == myColName:

aFilterHandle.FilterReference.TypeId = FilterTypeIdentifiers.ListBoxFilter

 

def DocPropertyList():

# Set Values As List from Document Property

mySelection = list(Document.Properties[DocPropMultiList])

return mySelection

 

def GetCurrentDict():

d = dict()

for x in Document.Data.Tables:

#Check Column Name vs List of Columns from Table

if myColName in [str(item) for item in x.Columns] :

# get filter

filter = Document.FilteringSchemes[0][x][x.Columns[myColName]]

# get current filter type

filterType = filter.TypeId

# change to checkbox to easily access unique value

filter.TypeId = FilterTypeIdentifiers.CheckBoxFilter

# Capture List of Items from CheckBoxFilter

mycountry_list = [value for value in filter.As[filters.CheckBoxFilter]().Values]

# return to old filter type

filter.TypeId = filterType

#Append to Dict

for item in mycountry_list:

d.setdefault(x.Name, []).append(item)

return d

 

def GetMyUpdateDict():

d = GetCurrentDict()

mySelection =DocPropertyList()

for i in d:

#Combine With DocPoperty List

combined = list(set(mySelection) & set([x for x in d]))

#Update Dictonary With Matching Values Only

d.update({i:combined})

return d

 

def RunFilters():

MyDict = GetMyUpdateDict()

myPanel = Document.ActivePageReference.FilterPanel

for item in myPanel.TableGroups:

#print(item.Name)

if item.GetFilter(myColName):

myString = ",".join(MyDict[item.Name] )

myFilter = item.GetFilter(myColName)

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

lbFilter.IncludeAllValues=False

strVals = myString

if strVals!=String.Empty:

print(myString, item.Name )

lbFilter.SetSelection(strVals.split(','))

else:

print(myString, item.Name )

lbFilter.IncludeAllValues=False

 

def ClearFilters():

myPanel = Document.ActivePageReference.FilterPanel

for item in myPanel.TableGroups:

if item.GetFilter(myColName):

myFilter = item.GetFilter(myColName)

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

lbFilter.Reset()

 

def Clear_or_Filter():

ChangeFilterstoListBox()

if Document.Properties[DocPropMultiList] != None:

RunFilters()

else:

ClearFilters()

 

Clear_or_Filter()

[K Rojas]

Thank you. I think this is exactly what I am looking for. Could you briefly describe how you implement this in Spotfire I am familiar with Python, but implementing it in Spotfire is completely new to me. 

[K Rojas]

Hello, 

 

I guess a better question is: how is myColName being passed to the script 

 

Thank you,