Modifying pivot and join conditions using IronPython

[Mark Herrmann]

Hi there

I'm pivoting three different data sets either on production item (WAFER_NAME) level oder on production item plus coordinate level (WAFER_NAME, F_X, F_Y) and the jioning these three together either just on WAFER_NAME or on WAFER_NAME, F_X and F_Y. I need an IronPyhton script which allows toggling back and forth between these two approaches.

Based on this I got as far as identifying the pivoting rules but I am failing to modify them. Also I found nothing for how to modify the join conditions in the data table.

Attached is a simple mock-up which contains the below script. If anyone's got an idea how to modify the pivot settings and/or subsequently the join conditions, that would be awesome.

# Copyright 2017. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

 

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Data.Transformations import *

from Spotfire.Dxp.Data.DataOperations import *

from System.Collections.Generic import List

 

#table=Document.ActiveDataTableReference

table = Document.Data.Tables['Correlation Table by Wafer, X, Y']

sourceview=table.GenerateSourceView()

 

 

 

operation= None

#get DataTransformations

i=0

for op in sourceview.GetAllOperations[DataTransformationsOperation]():

for t in op.GetTransformations():

i=i+1

print op.DisplayName,"#",i,":"

if (op.CanReplaceTransformations()==True and t.Name=="Pivot"):

j=0

for cat in t.CategoryColumns:

j=j+1

print " CategoryColumn #",j,":",cat

j=0

print " IdentityColumns:",t.IdentityColumns

for id in t.IdentityColumns:

j=j+1

print " IdentityColumn #",j,":",id

# would need to toggle the identity columns from WAFER_NAME, F_X, F_Y to just WAFER_NAME and back

# but to not get from the documentation how to adjust the below code

#newtrans=List[DataTransformation]()

#column = DataColumnSignature("WAFER_NAME"

#t=ReplaceValuesTransformation(column, "value1", "value2")

#newtrans.Add(t)

#op.ReplaceTransformations(newtrans)

#

# after modifying the pivots I would need to alter the join conditions accordingly...

# is joins=table.AddColumns() a good starting point or will that add a new additional join

[Gaia Paolini]

I cannot find an API for replacing the pivot transformations, I will inquire.

In the meantime, I would like to suggest an alternative solution:

A - create manually a different table that does the alternative pivot and join , call it e.g. 'Correlation Table by Wafer'

B - create a script to toggle the visual (rather than the table), something like this:

 

 

#Copyright 2022. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Application.Visuals import *

table1 = Document.Data.Tables['Correlation Table by Wafer, X, Y']

table2 = Document.Data.Tables['Correlation Table by Wafer']

page=Document.ActivePageReference

found_visual=False

for visual in page.Visuals:

if visual.Title==table1.Name:

found_visual=True

myvisual=visual

mytable=table2

print ('found table 1')

elif visual.Title==table2.Name:

found_visual=True

myvisual=visual

mytable=table1

print ('found table 2')

if found_visual:

visual_toggle=myvisual.As[TablePlot]()

visual_toggle.Title=mytable.Name

visual_toggle.Data.DataTableReference=mytable

columns = visual_toggle.TableColumns

visual_toggle.Columns.Clear()

for col in mytable.Columns:

columns.Add(col)

[Gaia Paolini]

Here is another source of information, although it would need translating to IronPython

Via the source view API, it is possible to change both transformations (you replace the old transformations) as well as changing join-settings (via the AddColumnsOperation).

Seehttps://community.spotfire.com/wiki/tibco-spotfire-data-table-source-view-api-overview#toc-18for an example of how the join type is changed

[Gaia Paolini]

and another example

https://community.spotfire.com/wiki/how-change-join-type-add-columns-operat...

[Mark Herrmann]

Thanks for looking into this, Gaia!

 

"In the meantime, I would like to suggest an alternative solution:

A - create manually a different table that does the alternative pivot and join , call it e.g. 'Correlation Table by Wafer'

B - create a script to toggle the visual (rather than the table), something like this:"

I had thought about having two tables and toggling the visuals as you suggested. But since several data functions depend on the table and because I was afraid to confuse users I wanted to restrict it to a single table.

"I cannot find an API for replacing the pivot transformations, I will inquire"

I could not find it either and I have to say the API documentation is extremly basic to say the least. But with lots of googling and adopting similar concepts from other Data Table Source View code snipets I think I now found it (see attached dxp). Still need to make it robust agains a few edge cases (e.g. when the INL table has no data) but I'm getting there.

 

Thanks,

Mark