Jump to content

Spotfire: Group By / Combine two columns

Xav Dou

Recommended Posts


I would like to combine the two columns "User id Aproval" into one column (one invoice needs two approval) and group theamount of invoice approved per user.

I attached one example.

I tried different method (groupby, cross table without success), any help please

Link to comment
Share on other sites

You have to unpivot your data table. (This is a un-group transformation)

Add a new data table and pick the original date table as input. Then pick 'unpivot' as transformation and click on 'add':


You will end up with this table:


I don't really understand why you want to have your output like you have it in your snapshot. But to achieve this use a cross table:

Link to comment
Share on other sites

Try this:

first add an unpivot transformation to your table, so that you pass through ID and Invoice, and transform User ID Approval 1 and User ID Approval 2. Say you call the Category 'Approval Order' and the Value 'User ID'.

Then on the resulting table calculate column [TotalApprovals] = sum([invoice]) over ([userID])

Then to avoid showing the duplicate rows, create a cross table with Horizontal=None, Vertical=UserID and cell values =Avg([TotalApprovals]) which you can rename to 'Invoices Approved' for display purposes. Maybe reformat so it does not show the decimals.

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