Joining Two tables on two fields

Tue, 19 Jul 2011 16:01:10 GMT

Hi everyone,

I am finding it hard to join two tables together.

I have a Master table which has PRFUID, incdate, incnumber, formtype and chiefcomplaint

This is loaded via SQL as a seperate table and stored into a QVD.

I have a Patient table which has PRFUID, incdate, incnumber and patientkey

This is loaded via SQL as a seperate table and stored into a QVD.

I need to have one table which has the PRFUID, incdate, incnumber, formtype and patientkey within it

Here is where I am struggling.

There are several form types, Each form is scanned in and given its own PRFUID and some of them don't capture the cheifcomplaint. A patient can have two forms, Form A with a PRFUID of 1001 and form B with an PRFUID of 2002. But both forms will have the incdate and incnumber on them.

I did join the patient table to the master table as below to get the incdate and the incprfno to build a string that would form a 'key'. I could then use this to link the patient table back to the master table and pull out the cheif complaint.

How can I join them together? This is what I have accomplished so far, but it is slow and I fear it is causing heavy CPU useage on a limited load.

TEMP_Patient:
LOAD 
 1 as counter_patient
 ,PRFUniqueId as %KEY_PRFUID
 ,NUM(PatientNo) as PatientNo
 ,EDAccessNum
 ,ContactNumber
 ,AUTONUMBER(upper(anPatFirstNames)&upper(anPatSurname)&patientDOB) as UniquePatientID
    ,UPPER(anPatSurname) as anPatSurname
    ,UPPER(anPatFirstNames) as anPatFirstNames
    ,UPPER(anPatGP) as anPatGP
    ,UPPER(ethnicGroup) as ethnicGroup
    ,applymap('Gender', patSex, NULL()) AS Gender
    ,patientDOB
    ,UPPER(IF(ISNULL(GPPracticeCode),anPatGPSurg,GPPracticeCode)) AS GPPracticeCode
    ,UPPER(patAddress) as PatientPostcode
 ,left(trim(patAddress),index(trim(patAddress),' ')-1) as PatientPostcodeShort
//Calc Age from incDate Late
    ,IF(ISNULL(PatAgeYearsMonths),patAge,PatAgeYearsMonths) AS patAge
    ,TRIM(NHSNumber) as NHSNumber
//I have joined the Master table in SQL here to obtain the incdate and incPrfNo.
    ,num(IncDate)&'-'&num(IncPrfNo) as %KEY_Patient
 ,DATE(IncDate,'dd/mm/yyyy') AS Inc_Date
 ,NUM(IncPrfNo) as IncPrfNo
;
SQL SELECT *
FROM TCMPRF.dbo."LB03_PATIENT" pat
INNER JOIN
(SELECT PRFUniqueId FROM [TCMPRF].[dbo].[PE01_MASTER] 
where IncDate between '01-Nov-2008' and getdate()
//WHERE IncDate >= DateAdd(month,-48,GETDATE())
) prf
ON 
pat.PRFUniqueId = prf.PRFUniqueId
LEFT JOIN
 [TCMPRF].dbo.PE01_MASTER ma
 ON
 pat.PRFUniqueId = ma.PRFUniqueId
;

Qualify *;
UNQUALIFY 
  %KEY_Patient
//  ,%KEY_PRFUID
  ,%KEY_Patient
  ,Patient.ChiefComplaintOriginal
;
Patient:
Load 
 TEMP_Patient.counter_patient as counter_patient
// ,%KEY_PRFUID
 ,TEMP_Patient.PatientNo as PatientNo
 ,TEMP_Patient.EDAccessNum as EDAccessNum
 ,TEMP_Patient.ContactNumber as ContactNumber
 ,TEMP_Patient.UniquePatientID as UniquePatientID
    ,TEMP_Patient.anPatSurname as anPatSurname
    ,TEMP_Patient.anPatFirstNames as anPatFirstNames
    ,TEMP_Patient.anPatGP as anPatGP
    ,TEMP_Patient.ethnicGroup as ethnicGroup
    ,TEMP_Patient.Gender as Gender
    ,TEMP_Patient.patientDOB as patientDOB
    ,TEMP_Patient.GPPracticeCode as GPPracticeCode
    ,TEMP_Patient.PatientPostcode as PatientPostcode
 ,TEMP_Patient.PatientPostcodeShort as PatientPostcodeShort
    ,TEMP_Patient.patAge as patAge
    ,TEMP_Patient.NHSNumber as NHSNumber
//    ,%KEY_Patient as %KEY_Patient_test
// ,TEMP_Patient.Inc_Date as Inc_Date
 
Resident TEMP_Patient;
left join 
Load 
         %KEY_Patient
         ,Master.ChiefComplaintOriginal as Patient.ChiefComplaintOriginal
resident Master; 
DROP Table TEMP_Patient

Many thanks in advance for any guidance.

topic Joining Two tables on two fields in QlikView

Joining Two tables on two fields