Adding new column "by hand" to Excel worksheets that support main data table

[Steve Van Kirk]

Hi there,

I'm having trouble figuring out how to do something that I'm sure Spotfire must be able to do. I have a data table based on several supporting Excel worksheets. Each worksheet has been added to the data table separately using "Add rows" on the data canvas.

Now suppose that after the fact I want to add a new column to each supporting worksheet - perhaps to indicate whose spreadsheet it was so I could filter on it later. So the new column might be "Person", and for spreadsheet #1 the column would say "Suzy", for spreadsheet #2 it would say, "Gerald", etc. The full data table would then have a "Person" column and there would be a block of entries under each name. I could then filter on it during visualizations ("Here's Suzy's data... Here's Gerald's data... etc.")

This seems like it should be pretty straightforward, but I haven't figured it out yet. Any help would be greatly appreciated!

Thanks!

 

Steve

[Kirsten Smith (she/her)]

When you add the new rows, click on 'Settings for Added rows'. Then click on 'Identify origin of rows' to add the desired new column.

[Steve Van Kirk]

Thanks for the reply - I appreciate it! 

 

I do already implement what you've suggested, the problem is that during the data wrangling process I sometimes discover that I wish I had another column in certain data source spreadsheets. Or sometimes I discover the need for 2 or 3 extra columns.  I want to be able to add them at will and fill their contents with whatever.

 

In my example, suppose I'm doing data wrangling and realize that one of my spreadsheets doesn't have a "Location" column.  Ideally I'd simply go back to the data canvas, select the spreadsheet, add a column called "Location", and fill every cell with "Seattle", or whatever.  I haven't been able to figure out how to do this, yet it seems like it should be incredibly easy. 

 

And then what if a week later I decide I need another column for certain spreadsheets, and I want to call it called "Local Community" where I would label it "Maple Leaf" or "Captol Hill" or "Queene Anne" or whatever.

 

So in a nutshell my question is this: how do I create completely new columns in one of my source data spreadsheets long after the data has been imported into a dxp file, and fill those columns with whatever I want... numbers, strings, etc..

 

BTW, I realize I could just modify the actual Excel spreadsheet & re-import it.  But then I have to do this every time the spreadsheet changes (for example, when I add new data to it).  If I could add the column in Spotfire as a transformation, then I only have to do it once.  Whenever I re-import the spreadsheet because it has additional data, the new column gets added by Spotfire automatically rather than me having to remember, "Oh yeah - I've got to add the 'Location' column and fill it with 'Seattle'...", etc.

 

Thanks!

 

Steve

[Kirsten Smith (she/her)]

If there are some specifics you are looking at when adding this new column I would recommend that you look at using a case() statement. If it's less structured than that you can add a new column with a default value then use the 'Replace cell value' transformation where needed, which is recently added transformation. After that you could develop the column of data in Excel and use the "Add columns" function, although that could be cumbersome depending on how many rows have been added.

[Steve Van Kirk]

Thanks for your reply - I really appreciate it.  I think what was throwing me off is that:

 

1. to add a new column you have to do a "transformation" rather than "add column", and

 

2. Under transformation you have to select "calculate new column".  I assumed this was mathematical, or at the very least it seemed I little cumbersome or non-intuitive.

 

I will definitely use your suggestion in the future - thanks again!

 

Have a good one!

 

 

 

Steve