Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have a Qlikview document that is scheduled for reload via Qlikview Server. When it runs at 6:10am via the server the log indicates only 60K records were pulled for one of the DB queries in the Edit Script. This is incorrect as there are over 105K records that should be returned from the query. Rerunning the task later in the day corrects the issue and the log shows the 105K records. Also running the exact same query via SQL Mgt Studio at 6:00am, just prior to the scheduled reload, returns the 105K records. The Qlikview log file does not indicate any failure, nor do the Qlikview server logs.
Looking for any suggestions here...Thanks in advance.
Here is a portion from the log when it does not return the correct number of records (the eCW_Patient is the table in question):
3/17/2014 6:10:02 AM: 0062 WHERE EncounterDeleteFlag = 'N'
3/17/2014 6:10:02 AM: 28 fields found: LabsEncounterKey, RxHubEncounterKey, VitalsEncounterKey, StructuredDataFactEncounterKey, DiagnosisFactEncounterKey, ClaimFactEncounterKey, FactEncounterKey, DimEncounterKey, Visit Type, Visit Status, Encounter Type, EncounterDimProviderKey, Rendering Provider, DimPatientKey, DimPatientDataKey, Encounter Patient, PatientRegistryAccountNo, EncounterFacilityName, EncounterFacilityGroupName, FactEncounterID, Appointment Date, Encounter Appointment Calendar Date, EncounterAppointmentCalendarDate_YY, EncounterAppointmentCalendaYear, EncounterAppointmentCalendarQuarterWithYear, EncounterAppointmentScheduledStartTime, EncounterAppointmentReason, EncounterDeleteFlag, 1,123,715 lines fetched
3/17/2014 6:10:09 AM: 0065 eCW_Patient:
3/17/2014 6:10:09 AM: 0066 LOAD
3/17/2014 6:10:09 AM: 0067 DimPatientKey,
3/17/2014 6:10:09 AM: 0068 DimPatientKey as VaccinationsDimPatientKey,
3/17/2014 6:10:09 AM: 0069 DimPatientKey as ReferralDimPatientKey,
3/17/2014 6:10:09 AM: 0070 DimPatientKey as PatientDataPatientKey,
3/17/2014 6:10:09 AM: 0071 DimPatientKey as AdmissionEventDimPatientKey,
3/17/2014 6:10:09 AM: 0072 DimPatientKey as PatientInsuranceDimPatientKey,
3/17/2014 6:10:09 AM: 0073 PatientPID as [Patient PID],
3/17/2014 6:10:09 AM: 0074 PatientAccountNo as [Patient Account No],
3/17/2014 6:10:09 AM: 0075 MeditechSourceID as [Meditech Source ID],
3/17/2014 6:10:09 AM: 0076 MeditechPatientID as [Meditech Patient ID],
3/17/2014 6:10:09 AM: 0077 SystemFlag as [System Flag],
3/17/2014 6:10:09 AM: 0078 PatientMRN as [Patient MRN],
3/17/2014 6:10:09 AM: 0079 PatientFullName as [Patient FullName],
3/17/2014 6:10:09 AM: 0080 DATE(PatientBirthDate, 'MM/DD/YYYY') as [Patient Birth Date],
3/17/2014 6:10:09 AM: 0081 PatientAddress1 as [Patient Address1],
3/17/2014 6:10:09 AM: 0082 PatientAddress2 as [Patient Address2],
3/17/2014 6:10:09 AM: 0083 PatientCity as [Patient City],
3/17/2014 6:10:09 AM: 0084 PatientState as [Patient State],
3/17/2014 6:10:09 AM: 0085 PatientZip as [Patient Zip],
3/17/2014 6:10:09 AM: 0086 PatientDeleteFlag as [Patient Delete Flag],
3/17/2014 6:10:09 AM: 0087 PatientDeceased,
3/17/2014 6:10:09 AM: 0088 DATE (PatientDeceasedDate) as [DeceasedDate],
3/17/2014 6:10:09 AM: 0089 PatientSSN as [Patient SSN],
3/17/2014 6:10:09 AM: 0090 PCP as [Patient PCP],
3/17/2014 6:10:09 AM: 0091 eCWPCP as [Patient eCW PCP],
3/17/2014 6:10:09 AM: 0092 ProvidersMatch as [Patient PCP Match Flag]
3/17/2014 6:10:09 AM: 0094 SQL SELECT
3/17/2014 6:10:09 AM: 0095 [DimPatientKey]
3/17/2014 6:10:09 AM: 0096 ,[PatientPID]
3/17/2014 6:10:09 AM: 0097 ,[PatientAccountNo]
3/17/2014 6:10:09 AM: 0098 ,p.[MeditechSourceID]
3/17/2014 6:10:09 AM: 0099 ,[MeditechPatientID]
3/17/2014 6:10:09 AM: 0100 ,CASE
3/17/2014 6:10:09 AM: 0101 WHEN PatientPID <> -1 and MeditechPatientID <> '' THEN 'Both'
3/17/2014 6:10:09 AM: 0102 WHEN PatientPID <> -1 and MeditechPatientID = '' THEN 'eCW Only'
3/17/2014 6:10:09 AM: 0103 WHEN PatientPID = -1 and MeditechPatientID <> '' THEN 'Meditech Only'
3/17/2014 6:10:09 AM: 0104 ELSE 'Unknown'
3/17/2014 6:10:09 AM: 0105 END AS SystemFlag
3/17/2014 6:10:09 AM: 0106 ,[PatientMRN]
3/17/2014 6:10:09 AM: 0107 ,[PatientFullName]
3/17/2014 6:10:09 AM: 0108 ,[PatientBirthDate]
3/17/2014 6:10:09 AM: 0109 ,[PatientAddress1]
3/17/2014 6:10:09 AM: 0110 ,[PatientAddress2]
3/17/2014 6:10:09 AM: 0111 ,[PatientCity]
3/17/2014 6:10:09 AM: 0112 ,[PatientState]
3/17/2014 6:10:09 AM: 0113 ,[PatientZip]
3/17/2014 6:10:09 AM: 0114 ,[PatientDeceased]
3/17/2014 6:10:09 AM: 0115 ,[PatientDeceasedDate]
3/17/2014 6:10:09 AM: 0116 ,[PatientDeleteFlag]
3/17/2014 6:10:09 AM: 0117 ,[PatientSSN]
3/17/2014 6:10:09 AM: 0118 ,prov.ProviderFullName PCP
3/17/2014 6:10:09 AM: 0119 ,eCWprov.ProviderFullName as eCWPCP
3/17/2014 6:10:09 AM: 0120 ,CASE
3/17/2014 6:10:09 AM: 0121 WHEN prov.ProviderFullName = eCWprov.ProviderFullName THEN 'Y'
3/17/2014 6:10:09 AM: 0122 ELSE 'N'
3/17/2014 6:10:09 AM: 0123 END as ProvidersMatch
3/17/2014 6:10:09 AM: 0124 FROM "DW_DDS"."bi_dds".DimPatient p
3/17/2014 6:10:09 AM: 0125 LEFT JOIN "DW_DDS"."bi_dds".DimProvider prov
3/17/2014 6:10:09 AM: 0126 ON p.PatientPCPKey = prov.DimProviderKey
3/17/2014 6:10:09 AM: 0127 LEFT JOIN "DW_DDS"."bi_dds".DimProvider eCWprov
3/17/2014 6:10:09 AM: 0128 ON p.PatienteCWPCPKey = eCWprov.DimProviderKey
3/17/2014 6:10:09 AM: 0129 WHERE DimPatientKey <> -1
3/17/2014 6:10:09 AM: 0130 AND [PatientDeleteFlag] = 'N'
3/17/2014 6:10:09 AM: 26 fields found: DimPatientKey, VaccinationsDimPatientKey, ReferralDimPatientKey, PatientDataPatientKey, AdmissionEventDimPatientKey, PatientInsuranceDimPatientKey, Patient PID, Patient Account No, Meditech Source ID, Meditech Patient ID, System Flag, Patient MRN, Patient FullName, Patient Birth Date, Patient Address1, Patient Address2, Patient City, Patient State, Patient Zip, Patient Delete Flag, PatientDeceased, DeceasedDate, Patient SSN, Patient PCP, Patient eCW PCP, Patient PCP Match Flag, 60,298 lines fetched
3/17/2014 6:10:10 AM: 0132 PatientData:
3/17/2014 6:10:10 AM: 0133 LOAD
3/17/2014 6:10:10 AM: 0134 PatientDataPatientKey,
3/17/2014 6:10:10 AM: 0135 DATE(LastOfficeVisitDate) as [LastOfficeVisitDate],
3/17/2014 6:10:10 AM: 0136 DATE(NextOfficeVisitDate) as [NextOfficeVisitDate]
3/17/2014 6:10:10 AM: 0137 SQL SELECT DimPatientKey as PatientDataPatientKey,
3/17/2014 6:10:10 AM: 0138 LastOfficeVisitDate,
3/17/2014 6:10:10 AM: 0139 NextOfficeVisitDate
3/17/2014 6:10:10 AM: 0140 FROM "DW_DDS"."bi_dds"."vwPatientData_QV"
3/17/2014 6:10:31 AM: 3 fields found: PatientDataPatientKey, LastOfficeVisitDate, NextOfficeVisitDate, 68,739 lines fetched
Here is a portion of the log from a subsequent run where it does:
3/17/2014 8:07:58 AM: 0062 WHERE EncounterDeleteFlag = 'N'
3/17/2014 8:07:58 AM: 28 fields found: LabsEncounterKey, RxHubEncounterKey, VitalsEncounterKey, StructuredDataFactEncounterKey, DiagnosisFactEncounterKey, ClaimFactEncounterKey, FactEncounterKey, DimEncounterKey, Visit Type, Visit Status, Encounter Type, EncounterDimProviderKey, Rendering Provider, DimPatientKey, DimPatientDataKey, Encounter Patient, PatientRegistryAccountNo, EncounterFacilityName, EncounterFacilityGroupName, FactEncounterID, Appointment Date, Encounter Appointment Calendar Date, EncounterAppointmentCalendarDate_YY, EncounterAppointmentCalendaYear, EncounterAppointmentCalendarQuarterWithYear, EncounterAppointmentScheduledStartTime, EncounterAppointmentReason, EncounterDeleteFlag, 1,087,709 lines fetched
3/17/2014 8:08:08 AM: 0065 eCW_Patient:
3/17/2014 8:08:08 AM: 0066 LOAD
3/17/2014 8:08:08 AM: 0067 DimPatientKey,
3/17/2014 8:08:08 AM: 0068 DimPatientKey as VaccinationsDimPatientKey,
3/17/2014 8:08:08 AM: 0069 DimPatientKey as ReferralDimPatientKey,
3/17/2014 8:08:08 AM: 0070 DimPatientKey as PatientDataPatientKey,
3/17/2014 8:08:08 AM: 0071 DimPatientKey as AdmissionEventDimPatientKey,
3/17/2014 8:08:08 AM: 0072 DimPatientKey as PatientInsuranceDimPatientKey,
3/17/2014 8:08:08 AM: 0073 PatientPID as [Patient PID],
3/17/2014 8:08:08 AM: 0074 PatientAccountNo as [Patient Account No],
3/17/2014 8:08:08 AM: 0075 MeditechSourceID as [Meditech Source ID],
3/17/2014 8:08:08 AM: 0076 MeditechPatientID as [Meditech Patient ID],
3/17/2014 8:08:08 AM: 0077 SystemFlag as [System Flag],
3/17/2014 8:08:08 AM: 0078 PatientMRN as [Patient MRN],
3/17/2014 8:08:08 AM: 0079 PatientFullName as [Patient FullName],
3/17/2014 8:08:08 AM: 0080 DATE(PatientBirthDate, 'MM/DD/YYYY') as [Patient Birth Date],
3/17/2014 8:08:08 AM: 0081 PatientAddress1 as [Patient Address1],
3/17/2014 8:08:08 AM: 0082 PatientAddress2 as [Patient Address2],
3/17/2014 8:08:08 AM: 0083 PatientCity as [Patient City],
3/17/2014 8:08:08 AM: 0084 PatientState as [Patient State],
3/17/2014 8:08:08 AM: 0085 PatientZip as [Patient Zip],
3/17/2014 8:08:08 AM: 0086 PatientDeleteFlag as [Patient Delete Flag],
3/17/2014 8:08:08 AM: 0087 PatientDeceased,
3/17/2014 8:08:08 AM: 0088 DATE (PatientDeceasedDate) as [DeceasedDate],
3/17/2014 8:08:08 AM: 0089 PatientSSN as [Patient SSN],
3/17/2014 8:08:08 AM: 0090 PCP as [Patient PCP],
3/17/2014 8:08:08 AM: 0091 eCWPCP as [Patient eCW PCP],
3/17/2014 8:08:08 AM: 0092 ProvidersMatch as [Patient PCP Match Flag]
3/17/2014 8:08:08 AM: 0094 SQL SELECT
3/17/2014 8:08:08 AM: 0095 [DimPatientKey]
3/17/2014 8:08:08 AM: 0096 ,[PatientPID]
3/17/2014 8:08:08 AM: 0097 ,[PatientAccountNo]
3/17/2014 8:08:08 AM: 0098 ,p.[MeditechSourceID]
3/17/2014 8:08:08 AM: 0099 ,[MeditechPatientID]
3/17/2014 8:08:08 AM: 0100 ,CASE
3/17/2014 8:08:08 AM: 0101 WHEN PatientPID <> -1 and MeditechPatientID <> '' THEN 'Both'
3/17/2014 8:08:08 AM: 0102 WHEN PatientPID <> -1 and MeditechPatientID = '' THEN 'eCW Only'
3/17/2014 8:08:08 AM: 0103 WHEN PatientPID = -1 and MeditechPatientID <> '' THEN 'Meditech Only'
3/17/2014 8:08:08 AM: 0104 ELSE 'Unknown'
3/17/2014 8:08:08 AM: 0105 END AS SystemFlag
3/17/2014 8:08:08 AM: 0106 ,[PatientMRN]
3/17/2014 8:08:08 AM: 0107 ,[PatientFullName]
3/17/2014 8:08:08 AM: 0108 ,[PatientBirthDate]
3/17/2014 8:08:08 AM: 0109 ,[PatientAddress1]
3/17/2014 8:08:08 AM: 0110 ,[PatientAddress2]
3/17/2014 8:08:08 AM: 0111 ,[PatientCity]
3/17/2014 8:08:08 AM: 0112 ,[PatientState]
3/17/2014 8:08:08 AM: 0113 ,[PatientZip]
3/17/2014 8:08:08 AM: 0114 ,[PatientDeceased]
3/17/2014 8:08:08 AM: 0115 ,[PatientDeceasedDate]
3/17/2014 8:08:08 AM: 0116 ,[PatientDeleteFlag]
3/17/2014 8:08:08 AM: 0117 ,[PatientSSN]
3/17/2014 8:08:08 AM: 0118 ,prov.ProviderFullName PCP
3/17/2014 8:08:08 AM: 0119 ,eCWprov.ProviderFullName as eCWPCP
3/17/2014 8:08:08 AM: 0120 ,CASE
3/17/2014 8:08:08 AM: 0121 WHEN prov.ProviderFullName = eCWprov.ProviderFullName THEN 'Y'
3/17/2014 8:08:08 AM: 0122 ELSE 'N'
3/17/2014 8:08:08 AM: 0123 END as ProvidersMatch
3/17/2014 8:08:08 AM: 0124 FROM "DW_DDS"."bi_dds".DimPatient p
3/17/2014 8:08:08 AM: 0125 LEFT JOIN "DW_DDS"."bi_dds".DimProvider prov
3/17/2014 8:08:08 AM: 0126 ON p.PatientPCPKey = prov.DimProviderKey
3/17/2014 8:08:08 AM: 0127 LEFT JOIN "DW_DDS"."bi_dds".DimProvider eCWprov
3/17/2014 8:08:08 AM: 0128 ON p.PatienteCWPCPKey = eCWprov.DimProviderKey
3/17/2014 8:08:08 AM: 0129 WHERE DimPatientKey <> -1
3/17/2014 8:08:08 AM: 0130 AND [PatientDeleteFlag] = 'N'
3/17/2014 8:08:08 AM: 26 fields found: DimPatientKey, VaccinationsDimPatientKey, ReferralDimPatientKey, PatientDataPatientKey, AdmissionEventDimPatientKey, PatientInsuranceDimPatientKey, Patient PID, Patient Account No, Meditech Source ID, Meditech Patient ID, System Flag, Patient MRN, Patient FullName, Patient Birth Date, Patient Address1, Patient Address2, Patient City, Patient State, Patient Zip, Patient Delete Flag, PatientDeceased, DeceasedDate, Patient SSN, Patient PCP, Patient eCW PCP, Patient PCP Match Flag, 105,688 lines fetched
3/17/2014 8:08:11 AM: 0132 PatientData:
3/17/2014 8:08:11 AM: 0133 LOAD
A few suggestions:
Thanks
AJ
It could be that db is still updating at the time, especially if those are views you are reading from. So maybe most of the records are locked so not read. If those are tables, try using (with read uncommited) or nolock in your SQL query.
QV does not select what it reads, it reads whatever is available, based on the where clauses. I believe you should concentrate on the database side.
What build (Version & SR) of QV?
-Rob
well it looks like I might be wrong...
Intermittent data loss (QV not loading the full dataset from source)
So Rob's question makes a lot of sense....
A bit disappointed from Qliktech here if this holds...
Ioannis,
Looks like I cannot get to the link you posted. I get an authorization error for some reason. Can you post any relevant of the text here.
For over a year now we have experienced an intermittent problem where our QV reload script returned a success result but had not loaded the full dataset from a source table.
This was eventually proven by repeatedly running the load script against a static and isolated database and comparing the number of records loaded. Occassionally (e.g. 1 in 20 reloads) the loaded data was missing some rows. Often the loss was only a few rows (tens or hundreds) but occassionally the loss was thousands. Usually on the next reload the full data was loaded. We have seen the problem on QV10 and QV11 and experienced it with Oracle, MSSQL and Access data sources but haven't tried it with others. We have recreated the problem on QV desktop and QV server. Increasing memory or changing the connection driver didn't resolve the problem.
QlikTech have acknowledged this as a high priority bug 47189 and are currently working on a fix. They have not suggested a work around or said it is something unusual in our script but our load script is VERY simple. For each table in our source database we select all the data and write it to a QVD file for consumption in other QVW documents.
TableA:
LOAD *;
SQL SELECT * FROM DataTableA;
STORE TableA INTO "DataFiles/TableA.qvd";
DROP Table TableA;
A discussion exists in the general QlikCommunity here if you want to know more
http://community.qlik.com/message/228589#228589
Now fixed in QlikView 10 SR5, which was released on June 28th 2012:
Interesting issue (a bit worrying); I would add, for trace, the same select but only with a count of records before and after that statement.
I know we are on 11 but I'm trying to get the specifics. Will post back once I do.
I've been told we are on 11.0.11282.0.