Clear a table in Ironpython not storing remove operations in the data canvas and keeping columns and calculated columns

[Henry Heberle]

Is there a way to clear a table using IronPython and not track the Remove operations?

I'd like to create a "Clear" button that will clear all tables from the dashboard, and then users can save it empty. This is also useful for other reasons, like when we load new data, I want to clear some tables that are output from past analyses. All of these happen using on-demand data, activated by users, so it's very dynamic. They change the dashboard and save new "templates", so no data is needed in such templates.

I have tried many approaches using RowSelection(IndexSet(dataTable.RowCount, False)) and then combining with operations to Delete the "remove operations" automatically from the data canvas.

The problem is that when we use RowSelection(IndexSet(dataTable.RowCount, False)), the data canvas keep accumulating those remove operations... and after some analyses, they become a huge list of remove operations.

How can we achieve the "Clear tables" without this problem?

Additionally, if my tables have calculated columns that are based on other calculated columns, when the table is empty, they accuse errors, making many visualisations to be marked in red. But it's not an error, it's just that users haven't loaded data yet in the dashboard. How can we avoid showing such messages to the users? An alternative solution would be to add 1 row of data in every table, so it would be a "semi-clear" option. Instead of clearing them completely, I would just let one row of data. It's not ideal, because the visualisations and tables would still have data, but this would at least make the dashboard very light to save. However I couldn't achieve this without the "remove operations" being stored in the data canvas.

[Henry Heberle]

Can't find the Edit button here...

Correction for the post above, the code I referred is actually this: table.RemoveRows(RowSelection(IndexSet(table.RowCount, True)))

[Henry Heberle]

Extra: reporting bug about the community forum. In the image, I'm considered author of my comment but not of my post :)

[Anthony Alvarez]

Henry,

I have some comments on a different possible solution because I can't answer your question directly. If you are looking for a second design approach, consider using the following:

• Load in a base table with 1 row of values (A)
• Use an insert rows transformation to load your on demand tables (B)
• You can control the on demand settings in (B) using your IronPython or using document properties to either allow the data to load in or not.

This design will produce a single row of data from (A) or all of your data (B) + (A) depending on how you control your on demand settings.

[Henry Heberle]

This could work, but we have around 20 tables, so this approach would be a time-demanding workaround to maintain it.
We also constantly add/edit/remove columns, which could make the process really long, to keep these two tables with the same columns and column types synchronised, for every table of our dashboard.

So no IronPython solution is possible?

[Olivier Keugue Tadaa]

Hi Henry, we understand your request and have been working on it. 

As you pointed out, data operations/transformations done via the script are by design, recorded as standard ones. 

We have some ideas but so far none of them seems to work. 
We will let you know as soon as we find a working solution or workaround. 

 

 

[David Boot-Olazabal]

Hi Henry,

Which Spotfire version are you currently working on? And in case it's not the latest version, do you plan to upgrade soon?
We may have some workarounds, but they may depend on your Spotfire version.

Kind regards,

David

[Henry Heberle]

Thanks guys for the replies. Even if it's a 'no it's not possible', this already allows me to move on.

It's  version 11.4. I believe Bayer Crop Science (BCS) will only upgrade it when we have a good business justification like when Mods was introduced.
For me, the most critical is the readability of visualisation - e.g., problems with long labels (categorical columns) in both trellis and x-axis in visualisations with complex trellis. Same for box plots.

[David Boot-Olazabal]

Hi Henry,

Thanks for the feedback.
So, in your case, as workaround, you could then use the remove rows iron python script for a certain number of times before applying the delete remove rows script to get rid of the cluttered remove rows operations. Since you're on Spotfire 11.4 iron python scripts are the way to go (Action mods come in to play as of Spotfire 14.4).

This is a script to remove rows:
 

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

#Import Namespace
from Spotfire.Dxp.Data import RowSelection, IndexSet

#Clear your table designated by 'tbl' script parameter
tbl.RemoveRows(RowSelection(IndexSet(tbl.RowCount,True)))

#tbl - Script parameter referencing the data table from which all the rows need to be deleted.

 

And this is a script to delete the removed rows, leaving other transformations alone:
 

from Spotfire.Dxp.Data.DataOperations import DataSourceOperation
from Spotfire.Dxp.Data.DataOperations import DataOperation

#tbl is an input parameter of type Data Table.

## functions
def find_remaining_operations(sv):
	allOps = sv.GetAllOperations[DataOperation]()
	numOps=0
	for op in allOps:
		if type(op).__name__ == "RemoveRowsOperation":
			numOps=numOps+1
	return numOps

###---------------------------------------------
#Remove last RemoweRows operation
sourceView = tbl.GenerateSourceView()

#remove operation is destructive and sourceView is updated after every remove
while find_remaining_operations(sourceView)>0:
	op=sourceView.LastOperation
	if type(op).__name__ == "RemoveRowsOperation" and \
		sourceView.CanRemoveOperation(op):
			sourceView = sourceView.RemoveOperation(op)


#Find how many of these operations are left - this is now optional
#The document properties needs to exist already (type integer)
numRemoveRowsOperations=find_remaining_operations(sourceView)
Document.Properties['numRemoveRowsOperations']=numRemoveRowsOperations

Hope this get things stared a bit more.

Kind regards,

David