2 Replies Latest reply on Jul 2, 2018 5:20 AM by Jerry Flatto

    Join Error - Extra rows created

    Jerry Flatto

      I have data from a Point of Sales system.  The register has a row of data for each item sold (Type = SALE).  The PoS uses a separate row to track how the customer paid (cash or credit card) (Type = TENDER).  I would like to perform a join to add the Tender Type (cash or credit card) to the same row as the Sales data as an additional field.

       

      This should be able to be done by a join but I have found a few issues.  Assume “SALE” is the left side of the join and “TENDER” is the right side of the join.

       

      Not all SALES have a TENDER and these are valid sales.  This is not an issue as I can do a left join to make sure I include these SALES regardless of whether I have a TENDER. 

       

      The issue arises in that some SALES have multiple TENDER rows. Each SALE should have a single TENDER row associated at most and the extra TENDER rows are caused by the sales clerk mistakenly hitting the TENDER button more than once.  Now the join becomes a “many to many” join and I get extra rows of false data.  For example, if I have 5 rows of SALES and 3 TENDER rows associated (with 2 of the TENDER rows being false data), I end up with 15 rows of Sales data (10 duplicated rows) instead of the 5 rows that I should have.

       

      Is there any way in Tableau Prep to identify which transactions have more than one row of TENDER associated and filter/delete the rows so that only the first row of TENDER is kept so the join can be performed correctly as a “Many (SALES side) to One (TENDER side)”?

       

      Transaction ID is the unique identifier.  While it is a number, in reality it is a string.

       

      I have attached a sample csv file.