Spotfire: Group By / Combine two columns

[Xav Dou]

Hi,

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

[Fabian Duerr]

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:

[Gaia Paolini]

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.

[Xav Dou]

Ok looks good