Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
Needed your assistance to solve my issue.
Below is my Business requirement:
I need to calculate the productivity of each doctors. I have the scheduling data in one table .i.e. total hours worked by each employee and each of them might work in more than one task for a given day (not always). This multiple task is given a "category" to distinguish between the tasks.
2nd table , I have the transaction table which contains the total no. of patients that doctor saw per day. This transaction table also have two , three dimensions (E.g Patient type, Patient New/ Established) which we use it to calculate the statistics.
Problem:
In my load script, I have created a join between these 2 tables based on 2 field: Doctor, Date.
In my App, i have plotted the stats as below:
Total patient seen per hour in Clinic:
Count of patients/ SUM(total hours) under category "Clinic" (Category = 'Clinic').
The problem is If I apply a filter on any of the dimension in the transaction table (Patient Type, New/Established) my Pivot chart / any chart used for Calculation goes blank or Null. To test the data I plotted a table Chart and found that IF those dimension are added along with the schedule data it shows as blank or Null.
As per my requirement, I wanted Qlik Sense to apply the filter 1st and then do the calculation. This will help me to to drill down and get to the statistics.
Below is my load script:
//below is the Transaction table. The join is based on 2 fields: CLINICIAN, sch_date
LOAD
IPOP,
LAB_REC,
RAD_REC,
PHARM_REC,
SVC_CNT,
DR_STAFF_CATEGORY,
MNO,
DR_NO,
PAT_NO,
MED_DATE,
MED_DESC,
MED_TYPE,
ECLAIM_NO as CLAIM_ID,
PAT_VST_TYPE,
PAT_MED_TYPE,
UPPER(DR_NAME),
CLINICIAN, //link between transaction to doctor schedule
UPPER(ORDERING_CLINICIAN),
date(MED_DATE,'YYYY-MM-DD') as 'Sch_date' //link between transaction to doctor schedule
IF(PAT_VST_TYPE =1 and MATCH(MPT,1,3,8),'New Visit',IF(PAT_VST_TYPE =2 and MATCH(MPT,1,3,8),'Follow Up','N/A')) as 'New/Follow';
[VISIT_SUMMARY_VW]:
SELECT A."IPOP",
A."LAB_REC",
A."RAD_REC",
A."PHARM_REC",
A."SVC_CNT",
A."DR_STAFF_CATEGORY",
A."MNO",
A."DR_NO",
A."PAT_NO",
A."MED_DATE",
A."MED_DESC",
A."MED_TYPE",
A."ECLAIM_NO",
A."PAT_VST_TYPE",
A."PAT_MED_TYPE",
A."DR_NAME",
A."CLINICIAN",
A."ORDERING_CLINICIAN"
FROM "HOSPITAL"."VISIT_SUMMARY_VW" A, "HOSPITAL"."PAT_MED_CLAIM_V3" C WHERE A.ECLAIM_NO = C.CLAIM_ID
AND C.INS_CNT > 0 AND IPOP IN ('OP', 'DC','OTHR')
AND A.CLINICIAN = 'GD20624';
//Doctor schedule table. This table has the total hours worked on a given date by a doctor
[Qlik_Appt_Schedule_Actual]:
LOAD
Appt_Sch_ID,
// Doc_Name,
"LIC No" as "CLINICIAN", //link to transaction table;
date(Schedule_Date,'YYYY-MM-DD') as 'Sch_date', //link to transaction table;
Tot_Hours as "Total Hours",
Category,
Sub_Category,
Dept;
SQL SELECT
"Appt_Sch_ID",
//"Doc_Name",
"LIC No",
"Schedule_Date",
"Tot_Hours",
"Category",
"Sub_Category",
"Dept"
FROM "HISREPORT"."dbo"."Qlik_Appt_Schedule_Actual";
Can someone assist how to fix this problem?
- Sultan