How to delete rows, use concatenate and sum

[Ludovic DEUDON]

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

A B C

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:

A B C

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.

[Fabian Duerr]

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)

[Ludovic DEUDON]

Thank you so much !

 

That's exactly what i needed.