Jump to content

Dynamic Pivoting when Data source is PostgreSQL table

Geetika Saini 2

Recommended Posts

Hello Everyone,

I have a use case where I am pivoting one of the existing tables. As of now I am doing Pivoting in Database as dynamic pivoting is not possible using Spotfire's existing Transformation functionality.

I am receiving new data from the database at every 30-second interval, so using Spotfire functionality fails as it does not update the Pivot table with new data hence I am doing Pivoting in Database and importing the Table after pivoting from the database.

Now the issue is that even creating and managing Dynamic pivoting is getting complicated in DB.

I am looking for ideas if Python Data functions can help us solve this problem.

I am attaching the table samples. In the tables I am trying to pivot the column "answer", This column gets new data every minute or so and it can have new data value every time.I am looking for ideas on how we can create a dynamic column for newly received value in the column.

I will appreciate if you can "Dr. Spotfire" can throw light on this topic



Link to comment
Share on other sites

One naive approach is to copy the existing data table and apply pivot transformation to this copied table. This will keep your original table intact and you can simply import data into it from your database.

I am not sure what exactly you want to do with Python Data functions. Since you have only mentioned that you want to pivot the 'answer' column, I assume that you want to display unique values from it in separate datatable. If so, you can take [answer] column as input and apply the set function to it to get the unique values. Configure the output handler to get these unique values in a new data table.

You can refer to the attached analysis file (from 10.8 version).

Link to comment
Share on other sites

Hello Shashank,


I have attached an excel sheet, it contains two sheets.


Metric sheet- is the original table


Pivot data- it has the pivoted data for the "metric" column values pivoted which contains the corresponding values from "answer" column in rows for each submission id


Here I am looking to pivot the values from the "Metric" column and as this column keeps updating, hence I am not able to use simple pivot function from Spotfire as Pivot doesn't get updated automatically when new data comes in.


I am trying to create a scatter plot where I can showcase for example "CSAT" on the x-axis and "Food&Beverage" on Y-axis and the values for these corresponding points as points on the graph.


I am attaching the screenshot for more clarity.


I hope I am able to explain it better now.


/modules/file/icons/x-office-document.png new_doc_for_community.docx



Link to comment
Share on other sites

I don't see any attached excel sheet.


In general, if you have a file opened in Spotfire and meanwhile data gets updated at the backend, you won't see updated data unless you reload it or schedule it (for Web Player) irrespective of Pivot function. If you follow the first naive approach to test this and reload the data, it should work.

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...