Qlik data not matching validation file

[erickd1190]

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?

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;

[shraddha_g]

Please share sample app and sample data to understand issue

[vlad_komarov]

Erick,

Is this difference significant?

If not, it could be just a rounding error (Qlik might export table into spreadsheet with rounded numbers).

Please provide more details.

VK

[erickd1190 · Accepted solution]

I can't provide any data at this time but the difference is a couple hundred thousand.