3 Replies Latest reply: Mar 8, 2017 12:14 PM by Erick Dameron RSS

    Qlik data not matching validation file

    Erick Dameron

      Hi All,

       

      I am working on a production app and have found that when i sum the service rate it adds up to MORE than my excel file from the same data source.

       

      I have included a picture of my datamodel and my load script is below.

      Also is there a better way to validate the data within my app rather than generating an excel file?

       

      Data model.PNG

       

      My Load is set up like this:

       

      //Preceding Load

      LOAD *,

      transfer_date-visitdate AS Notedays,

      IF(transfer_date - visitdate<4,1,0) AS Compliant;

       

       

      History:

      LOAD

          visitdate,

          visitid,

          client_id,

          service,

          staffid,

          provider,

          cpt_code,

          program,

          transfer_date,

          "rate",

          emp_status

      FROM [lib://IT Drive- Reporting (tbh_erickd)/QVD Files\History.QVD]

      (qvd);

       

       

      LOAD

          "Employee ID" AS staffid,

          "Last Name First",

          EmployeeStatus,

          Site,

          "Last Hire Date",

          Program AS "E3.Program",

          "Position Description",

          "Program-Position Description",

          "Supervisor Name",

          CorporateLevelCode,

          "Termination Date",

          PT,

          IP

      FROM [lib://IT Drive- Reporting (tbh_erickd)/Qlik\Emp-Incentive Link.xlsx]

      (ooxml, embedded labels, table is [Emp-Incentive-Link]);

       

      // // Loads days since beginning of records

       

       

      MinMaxDate:

      LOAD Min(visitdate) As MinDate

       

       

      Resident History;

       

       

      LET vMinDate = PEEK('MinDate',-1,'MinMaxDate')-1;

      LET vMaxDate = FLOOR(MonthEnd(Today(1)));

       

       

      Drop Table MinMaxDate;

       

       

      // Master Calendar for fiscal year

       

       

      Calendar:

      Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,

        Dual(Month, fMonth) AS FMonth,

          DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,

          *;

        

      Load Year + IF(Month>=$(vFM), 1,0) As fYear,

        Mod(Month-$(vFM),12)+1 As fMonth,

          DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,

         *;

      Load visitdate,

      Year(visitdate) As Year,

      Month(visitdate) as Month,

      Week(visitdate) as Week

       

       

      Resident History;

       

       

      LOAD Date(recno()+$(vMinDate)) As visitdate AutoGenerate vMaxDate - vMinDate;