Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Please share sample app and sample data to understand issue
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