1 Reply Latest reply on Sep 22, 2016 1:33 AM by Pinky Sanni

    Joins with & without duplicates

    Pinky Sanni

      Hi All,

       

      I have 3 sample datasets - Sales, ShowroomStock & Modelreference that are related to Car sales & stocks.

       

      Sales - Contains the daily transactions done in the showrooms. (Related to ShowroomStock file by 'Showroom' & 'StockKey' and this combo is not unique)

      ShowroomStock - Contains the details of stock including the stock remaining ('Showroom' & 'StockKey' together is the Primary Key)(Related to Modelreference file by 'RefName')

      Modelreference - Contains the details of the Car models ('RefName' is the Primary Key)

       

      Now, the requirement is to build a worksheet which shows the 'Stock Left' and 'Num of Cars Sold' bar chart for a particular 'Brand' of Car as shown in the 'Cars.png' snapshot.

      But, the problem with that is that the StockLeft numbers are not correct and getting added up due to non-uniqueness(duplicate combo of Showroom & StockKey) of the 'Sales' data.

      Ideally, my StockLeft numbers should like the ones in 'Cars_SalesIgnored.png' snapshot; I achieved this by removing the 'Sales.csv' from the data source.

       

      Is there any way to achieve this without removing the driving table 'Sales.csv' from the data source (as I need to show the 'StockLeft' & 'NumofCarsSold' in the same worksheet). Also, I can't use Data Blending as the real-time data is very huge and performance is a constraint for me.

       

      PFA, the attached workbooks & screenshots for reference and let me know any solutions.

       

      Best Regards,

      Pinky