4 Replies Latest reply: Apr 4, 2016 6:40 AM by Stefan Wühl RSS

    Duplicate data issue

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

      Hi guys,

       

      I've problem with one load of my script. In source table total BILL for company AA is 505

      Source

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

       

      In file that I use for validation it also equal 505

      Validation:          

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

      But in my application it for some reason equeal 1120

      Application:  

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

       

      Can you please look at my script and find issue:

       

      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;

        • Re: Duplicate data issue
          Stefan Wühl

          It's most likely that your JOIN duplicated records.

           

          It's hard to tell in detail without knowing the input records of your SQL tables.

          Or maybe you linked an invoice multiple times to patients. Your first tables seems not to be just doubled in amount, there seems to be some amount shifting also.

           

          Could you post your document log?

           

          And, also important, on which version are you? There are issues with QV12SR1, so if you are on SR1, be warned that there are issues with JOIN.

          • Re: Duplicate data issue
            Avinash R

            The issue is with JOIN s and multiple loading statements....Try load the tables independently and allow the Qlikview to  form the association ..if everything is ok then try to play with your conditional and join statements 

            • Re: Duplicate data issue
              Perumal Ayyappan

              Joining creating duplicate value.

              Temporally use below expression in front end

               

              sum (aggr (Distinct Sum(Distinct [Open Receivables]),Patient))

              • Re: Duplicate data issue
                Stefan Wühl

                Besides all said about JOINs, aren't you comparing two different values?

                 

                In your first table, you show BILL values (aggregated?) per MEDNO.

                 

                In your last table, you are showing Open Receivables, which are derived from BILL using:

                 

                LOAD

                INVOICE,

                If(BILL > AMTPAID,

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

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

                ;

                SQL SELECT *

                FROM txBilling;

                 

                I also assume there are multiple records / transactions for a bill and you essentially want an aggregate here?

                 

                Like

                 

                LOAD

                INVOICE,

                If(Only(BILL) > Sum(AMTPAID),

                Only(BILL) - Sum(AMTPAID),0) as [Open Receivables],

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

                GROUP BY INVOICE

                ;

                SQL SELECT *

                FROM txBilling;

                 

                Not sure about the aggregation functions we need to use in the LOAD, that's depending on your requirements.