5 Replies Latest reply: Apr 4, 2016 4:38 AM by Tatsiana Chetirbok RSS

    Incorrect join

    Виталий Чуприна

      Hi All,

       

      I try to join two tables, the main promble is duplicated data [Open Receivables]:

      When I load data wihout join data looks correctly. I can't understand what the reason of issue

       

      JOIN:

        

      ACCTNUMMEDNOSum ([Open Receivables])
      Total 1120
      AA14627495
      AA20514260
      AA2395555
      AA29102310

       

      Without Join:

        

      ACCTNUMMEDNOSum ([Open Receivables])
      Total 505
      AA14627165
      AA20514130
      AA2395555
      AA29102155

       

      Facts:

       

      LOAD

      MEDNO,

      TX,

      INVOICE,

      ACCTNUM,

      IF (IsNull(QUANTITY), 0, QUANTITY) As QUANTITY,

      IF (IsNull(U_PRICE), 0, U_PRICE) As U_PRICE

      Where Year(DATE)>='1994';

      SQL SELECT *

      FROM Treat;


      Inner Join(Facts)

       

      LOAD 

      INVOICE,

      If(BILL > AMTPAID,

      BILL - AMTPAID,0) as [Open Receivables]

      SQL SELECT *

      FROM txBilling;