31 August, 2017

Create, get, remove datatable relations in Spotfire using Python

Create, get, remove datatable relations in Spotfire using Python

Imagine you have a data table with countries:

Country code Country name
HKG Hong Kong
ISR Israel
MYS Malaysia

And data table with languages related to these countries:

Country code Language Percentage
HKG Canton Chinese 88.7
HKG Chiu chau 1.4
HKG English 2.2
HKG Fukien 1.9
HKG Hakka 1.6
ISR Arabic 18.0
ISR Hebrew 63.1
ISR Russian 8.9
MYS Chinese 9.0
MYS Dusun 1.1
MYS English 1.6
MYS Iban 2.8
MYS Malay 58.4
MYS Tamil 3.9

Where "Country code" is the common field.

You would like to create link between these tables. It is possible to create relation manually in Spotfire, but for some reason you need to create it automatically via Python script.

Create relation between tables

def add_relation(table1, table2, link_expression):
    Document.Data.Relations.Add(
        Document.Data.Tables[table1],
        Document.Data.Tables[table2],
        link_expression)

Usage:

add_relation("country", "lang", "[lang].[Country code]=[country].[Country code]")

Now you are able to select country in the "country" table and all related languages from the "lang" table will also be selected.

Like here:

Spotfire linked tables

Get relation expression

def get_relation(table1, table2):
    return Document.Data.Relations.FindRelation(
        Document.Data.Tables[table1],
        Document.Data.Tables[table2])

Usage:

print(get_relation("country", "lang").Expression)
# [lang].[Country code]=[country].[Country code]

Delete relation

def del_relation(table1, table2):
    relation = Document.Data.Relations.FindRelation(
        Document.Data.Tables[table1],
        Document.Data.Tables[table2])
    if relation:
        Document.Data.Relations.Remove(relation)

Usage:

del_relation("country", "lang")

See more about Spotfire on GitHub

Xantorohara, 2017-08-31, Spotfire 7.8.0

No comments: