6 Replies Latest reply on Sep 25, 2014 11:09 PM by Elliott Grant

    Joining creates duplicated rows, blending creates asterisks.  Is there an answer that doesn't involve flattening in the ETL?

    Elliott Grant

      Apologies in advance for the N00b question.

       

      I have 2 tables: 

      The primary contains a series of product inspections, indexed with a unique inspection ID, called GroupID.  The inspection also records the supplier name and a quantity of cases inspected.

      The secondary contains the inspection results, indexed again by GroupID.  An inspection can result in multiple 'defects' being recorded - and hence several rows with the same GroupID.

       

      Here's the problem:

      • When I left join the tables, the resulting table duplicates rows for inspections with more than one defect - and hence causes the case counts to multiple.  Trying to get back to the right case count with AVG or SUM([Number of Records]/COUNTD([GroupID]) results in a sticky mess of AGGREGATES.
      • When I try to blend the data instead (as recommended in some KB) using GroupID and the link, I either get * for inspections with more than one defect, or counts that only include inspections with defects.

      I've created a sandbox and have no problem calculating the results I expect in EXCEL - but I can't get there in Tableau 8.2.  You can see I'm trying to calculate % of total inspections that had a particular defect - so AGG doesn't work. 

      Attached are my attempted join and blends as .twbx

      I've fiddled with WINDOW_SUM and its friends with no luck.

      Many thanks to the community in advance!

       

      Screen Shot 2014-09-24 at 9.23.53 PM.png