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

    Incorrect join

    Vitalii Chupryna

      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;

        • Re: Incorrect join
          Jonathan Dienst

          Please align your example with the problem script - where your value INVOICE?

           

          It looks like this value is not unique in txBilling, so you might need a group by in your SQL statement.

            • Re: Incorrect join
              Vitalii Chupryna

              Hi Jonathan,

               

              I've checked Treat and txBilling table. In txBilling table INVOICE values is not unique

               

              MEDNOTXINVOICEACCTNUMQUANTITYU_PRICEDATE
              14627323824CHECK1209/11/1992
              14627623824CHECK1209/11/1992
              14627823824CHECK1309/11/1992
              146272823824CHECK1409/11/1992
              146272923824CHECK1089/11/1992
                • Re: Incorrect join
                  Jonathan Dienst

                  >>In txBilling table INVOICE values is not unique

                   

                  Well there's your problem. Either sum the quantity and u_price values and group by the invoice #, or you will need to reconsider your model design.

                  • Re: Incorrect join
                    Tatsiana Chetirbok

                    if you join with you Treat table

                    MEDNOTXINVOICEACCTNUMQUANTITYU_PRICEDATE
                    14627323824CHECK1209/11/1992
                    14627623824CHECK1209/11/1992
                    14627823824CHECK1309/11/1992
                    146272823824CHECK1409/11/1992
                    146272923824CHECK1089/11/1992

                    txBilling table

                    INVOICEOpen Receivables
                    23824100

                    you get this result

                    MEDNOTXINVOICEACCTNUMQUANTITYU_PRICEDATEOpen Receivables
                    14627323824CHECK1209/11/1992100
                    14627623824CHECK1209/11/1992100
                    14627823824CHECK1309/11/1992100
                    146272823824CHECK1409/11/1992100
                    146272923824CHECK1089/11/1992100

                    so, when your INVOICE doesn't unique in Treat it is not correct to join this table

                • Re: Incorrect join
                  Tatsiana Chetirbok

                  can you post your data from Treat and txBilling without joining?