Jump to content

How to delete rows, use concatenate and sum

Ludovic DEUDON

Recommended Posts

Hello everyone, i'm Ludo from France and i'mcurrently starting to work with Spotfire formy work.

I'm discovery the tool and i need help for one task.

For example :

Column A : Supplier

Column B : Country

Column C : Volum


1 | Supplier A| France 75 m3

2| Supplier A| Espagne 80 m3

3| Supplier A| Italie 20 m3

4| Supplier A| USA 40 m3


I would like to get only one row with:


Function concatenate Total sum of 75m3 + 80m3 ...

1 | Supplier A| France|Espagne|Italie|USA 215m3

And of course delete row 2, 3, 4... I know functions that i have to use but i don't really know how to do on spotfire... Maybe use python

Thank you for your attention.

See you soon.

Link to comment
Share on other sites

You are looking for a simple Pivot operation. Depending on your needs you could use a cross table or you could create a new table with a Pivot operation.

In both cases you have to ensure that your column C contains numeric values, because you want to sum up the values. So, either re-calculate and replace this column (transformation) or add a calculated column. Check the available text function RXreplace()to strip/replace "m", then trim the string to get rid of any spaces with Trim() and convert it to a numeric value with Real(). You can do all this in one line of code.

The final cross table should look like this (using Sum() and UniqueConcetenate())


Check the documentation for further info about pivot transformation if you want to create a new table instead of a cross table (which is just a visualization)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...