1 Reply Latest reply: Apr 4, 2016 4:45 AM by Gysbert Wassenaar RSS

    Duplicated data issue

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

      Hi Guys,

       

      I've a problem with one table, data all time duplicated. For example company AA have following data:

       

      MEDNOACCTNUMINVOICEBILLDATEAMTPAID
      14627AA5555216511/6/19960
      20514AA568971301/2/19970
      29102AA569701551/6/19970
      23955AA9700500553/18/19970

       

      In report I should have following (Sum equal 505):

               

      AccountCurrent1 - 3031 - 6061 - 9091 - 120121 - 150151 +Total AR
      AA$0$55$0$285$0$165$0$505


      My report (data for first period looks correctly, second multiplied by 2 , third multiplied by 2 , fourth multiplied by 3 ) :  

       

      ACCTNUMPatientSum ([Open Receivables])
      1120
      AA23955 - SHARMA,  B55
      AA20514 - MCCORMACK,  M260
      AA29102 - BARLTROP,  G310
      AA14627 - ABRAMOVITZ,  M495

       

      See part of  script below:

       

      ODBC CONNECT TO [MS Access Database;DBQ=C:\Users\Админ\Desktop\TestDevelop\Test\Data\QVPreSalesData.mdb];

       

       

      Facts:

      LOAD

      MEDNO,

      TX,

      INVOICE,

      ACCTNUM,

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

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

      Date(ApplyMap('DateMap', DATE, Null())) as DATE

      Where Year(DATE)>='1994';

      SQL SELECT *

      FROM Treat;

       

       

      Billing:

      Join (Facts)

      LOAD *,

        IF($(varMaxDate) - Num(B_DATE)=0, 'Current',

        IF($(varMaxDate) - Num(B_DATE)<=30, '1-30',

        IF($(varMaxDate) - Num(B_DATE)<=60, '31-60',

        IF($(varMaxDate) - Num(B_DATE)<=90, '61-90',

        IF($(varMaxDate) - Num(B_DATE)<=120, '91-120',

        IF($(varMaxDate) - Num(B_DATE)<=150, '121-150',

        IF($(varMaxDate) - Num(B_DATE)>150, '151+'))))))) AS Receivebles_Age;

      LOAD

      INVOICE,

      If(BILL > AMTPAID,

      BILL - AMTPAID,0) as [Open Receivables],

      Date(ApplyMap('DateMap', DATE, Null())) as B_DATE

      ;

      SQL SELECT *

      FROM txBilling;

       

       

       

       

      LOAD *,

      MEDNO&' '&'-'&' '&LNAME&','&'  '&FNAME AS Patient;

      LOAD

      MEDNO,

      LNAME,

      If(IsNum(FNAME) OR FNAME=' ', 'N.A.', FNAME) AS FNAME;

      SQL SELECT *

      FROM Patients;