8 Replies Latest reply: Mar 18, 2014 10:00 AM by Eric Hyland RSS

    Qlikview Reload does not pull all database records

      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