1 Reply Latest reply on Oct 27, 2017 10:05 AM by Stoyko Kostov

    Join Problem

    prabhjot singh

      Hi all

      I am facing one problem while joining two excel data sources. I have one file Parts sales report from sales and other Parts supply report from store. My clients issues parts in many times from stores and sales them in one time or less then issues. The problem is we do not have parts sales price and cost price in Parts sales report. So we have to join them to get prices. While we are joining them we have less entries of sales in Parts sales report and more in Parts supply report. So when i am joining them with left join then it duplicate the rows and add rows from parts supply reports.

      for Eg.

      We have three entries of a particular parts with quantity 20,40,40  parts supply reports. and we have one entry of that part  with quantity 100 then it is showing three different rows after joining them.

      Here is one entry of both data files. in parts supply report there are three entries with qnty 71,25,37 and in parts sales reports there is only one entry with total of all three 133, but in join there are three entries with qnty 133.

       

       

      Join file

           

      Bill No. (Sheet1)Bill No.Rs 10 FilterRef Doc LocationCategory - PartsAcc profitParts ProfitProfitRev/ProfitCancel (Sheet1)CancelCost (Sheet1)CostCust. CodeCust. Name (Table 1)Cust. NameCustomer Code (Sheet1)Customer CodeDate (Table 1)DateDiscount % (Sheet1)Discount %Discount (copy 2)Discount (copy)DiscountF15 (Sheet1)F15F17 (Sheet1)F17F18 (Sheet1)F18F2 (Sheet1)F20Item No. (Sheet1)Item No.Job CodeJob Desc.Job Ord. No.Job TypeLabour CostLocation (Acc Part Supply)Location (Sheet1)Location (Table 1)Location1ModelNet Amt.Net Price (Sheet1)Net PriceNew Job Order NoNo. (Sheet1)No.Number of RecordsOil CostOutside CostPart Desc.Part No. (Acc Part Supply)Part No. (Sheet1)Part No. (Table 1)Part No.1 (Sheet1)Part No.1Part No.Parts Cost (Table 1)Price / Unit Sale (Sheet1)Price / Unit SaleProfit(%) (Sheet1)Profit(%)Qty.Ref. Doc. No.Reg. No.Sale DateSale Qty. (Sheet1)Sale Qty.Sub TotalTech.Total Amt.Total Cost Amount (Sheet1)Total Cost AmountTotal Sales Amount (Sheet1)
      C17-000086C17-000086More than Rs.10BPJ16-02327BTDBP33.372637.39019.3660000001Service  Workshop0000542737BTDBTDKUN40R-GKMDYX2637.391407.931Paint Material-Metallic(10gms)Z-PNT01-MET01Z-PNT01-MET0119.832.370146133BPJ16-0232742737712637.391374.561407.93
      C17-000086C17-000086More than Rs.10BPJ16-02327BTDBP17.392637.39019.3660000001Service  Workshop0000742737BTDBTDKUN40R-GKMDYX2637.39733.711Paint Material-Metallic(10gms)Z-PNT01-MET01Z-PNT01-MET0119.832.370146133BPJ16-0232742737372637.39716.32733.71
      C17-000086C17-000086More than Rs.10BPJ16-02327BTDBP11.752637.39019.3660000001Service  Workshop0000642737BTDBTDKUN40R-GKMDYX2637.39495.751Paint Material-Metallic(10gms)Z-PNT01-MET01Z-PNT01-MET0119.832.370146133BPJ16-0232742737252637.39484495.75

       

       

      Parts sales reports

       

           

      Cust. CodeCust. NameRef. Doc. No.RefX & WBill No.Accc PartSale DateModelPart No.Part Desc.Qty.Total Amt.DiscountNet Amt.Location
      60000001Service  WorkshopBPJ16-02327BP#N/AC17-000086#N/A02-01-2017KUN40R-GKMDYXZ-PNT01-MET01Paint Material-Metallic(10gms)1332637.3902637.39BTD

       

       

       

       

      Parts supply report

           

      Bill No.Sale Qty.CancelPrice / Unit SaleCostTotal Sales
      Amount
      Total Cost
      Amount
      Discount %AmountNet PriceProfit(%)
      Item No.LocationPart No.1
      C17-00008671019.8319.361407.931374.56001407.932.3701460000502-01-2017BTDZ-PNT01-MET01
      C17-00008625019.8319.36495.7548400495.752.3701460000602-01-2017BTDZ-PNT01-MET01
      C17-00008637019.8319.36733.71716.3200733.712.3701460000702-01-2017BTDZ-PNT01-MET01
        • 1. Re: Join Problem
          Stoyko Kostov

          Hi Prabhjot,

           

          Can you describe what you are trying to achieve?  Why is the current result not the one you would like to get?

           

          Looking at your example, I assume you are joining on [Bill No.].  Is this correct? 

           

          The definition of a join is the following: For each row in table A and each row in table B where the joining field matches, you would get a new row in table [A join B].  Namely, if you have n rows in table A and m rows in table B where the joining field matches, you would get n*m rows in [A join B].  This is regardless of the type of join that you use (inner, left outer, right outer, full outer).  The type would make a difference only in the cases where you have no matching rows for certain join field values.

           

          The join table would also contain every column from A and every column from B.

           

          Since you have one row with Bill No. C17-000086 in Parts sales reports and 3 in Parts supply report, in the joined table you would get 1*3 = 3.

           

          For a simple example, if table A has rows (1,2), (1,3), (1,4) and table B has rows (1,5), (1,6) and you are joining on the first column in each table, your [A join B] table would have 6 rows:

          (1,2,5), (1,2,6), (1,3,5), (1,3,6), (1,4,5), (1,4,6).

           

          Let me know if you have more questions, and I'd be happy to help.  Most importantly, please state what result you are trying to achieve.

           

          Thank you!