Columns inserted with pivot transformation disappear when replacing data tables by new data - looking for solution or alternative
I'm working on a spotfire dashboard with 2 data tables.
Here is the situation :
Table A : One row is for one document number
Table B : Multiple rows for one document number (list of all transactions)
I am using a pivot transformation to insert columns in Table A this way :
Table A (with pivot transformation)
|Company||Document number||Number of transactions||Total amount||Accounts list|
|A||1||4||630||A, A, B, Z|
|A||2||3||55||B, C, L|
This new table is the base of my data visualisation and especially the new columns I'm inserting by pivot transformation.
This dashboard is supposed to be able to automatically update when I replace my original data tables by new external data (for example when i need to visualize the dashboard for a company B). However, when I replace the data tables by new external data spotfire removes all the columns inserted by pivot transformation. So every time I replace data tables I need to insert the new columns manually.
So I'm looking for 2 solutions :
- Is there any way that could make the columns inserted by pivot transformation automatically update with new data ?
- If it is not possible, are there any alternative solutions that would help solve the problem ? Any ideas ?
Thank you very much !