CODEWITHSUNDEEP
left-joinpivot-tablespotfirefull-outer-join
amidatti
amidatti

Reputation: 33

Column match Spotfire pivot

I have a table of estimates by team and a table of spend by team. I made a pivot table using the estimates as the source table and created a column match by team so I could display estimate and spend in the same visualization. But there are teams in the spend table that are not included in the estimates table, and the result is that they don't get included in the pivot. These extra teams need to show up so we can catch teams that are spending on the project that haven't been estimated for, or to identify spend rows that have nulls in the team column. I suppose if we were imagining this scenario as adding columns in the data canvas, it would be akin to a full outer join, but the pivot result currently looks like a left outer join. Making spend the source table is problematic in the same way, because some of the teams in the estimate have no spend records yet.

Upvotes: 1

Views: 242

Answers (1)

Tyger Guzman
Tyger Guzman

Reputation: 758

You can join the two data tables with a full outer join and forgo column matching.

Or you can create a third table with all variations/line items them use it as the main table for your cross table having column matching to your other two tables.

Upvotes: 1

Related Questions