Replace data source on an excel is removing all transformations in analysis

[Manju Goyal]

I have an excel sheet as a data source. There are many transformations made on it once it loads into Spotfire. Multiple times, rows are added to it via existing data tables in analysis. Issue is when I replace the first excel with an excel from a new location (location changed but the column names etc. remaining the same), all transformations done after that are disappearing, including the various joins for the addition of rows. I really need to replace the data source and if I have to manually go back and create all those transformations back again, it is becoming a very time consuming process. Does anyone know why this is happening or if there is a work around it

[Jose Leviaguirre]

There is a difference between replace data source and replace data table. It looks like you are replacing the data table and not the data source. Please confirm what steps you are taking and what version of Spotfire you are using.

Replacing a data table is good when it comes to have all the transformation you need and you need to replace it with another data table that has all the joins and transformations as your final data table. Usually I work of a small subset on an excel file that mimic different data tables from a database and I am working on a requirement from the data team to produce views a materialized tables directly on the data base side. When doing this, all the transformations are gone as you are describing.

I often use the replacing a data source feature when it comes to switching from a dev environment to UAT or prod. That should not affect the transformations, joins or unions you might have, unless perhaps it's a derived table or you are using an existing table to create others.

[Kevin Flynn]

I found the easiest way to deal with Excels that have to be updated is below:

Create an Analysis that just has the Excel File.

Export Data to Library

Save the Analysis file as "Load Excel"

Change your Analysis that is using Excel to hit the Data loaded in Library.

Once this is done, the only thing that needs to be done is to open up the "Load Excel" Analysis, Replace the data table, Then export the data over the same Data that you exported previously.

This will update all the analysis that will hit the Data Loaded in library.