Re: Beginner trying to make sense of this code - Qlik Community

Anonymous · ‎2017-11-13

Hello Qlik Community,

I'm not sure if i posted this in the right place, I'd appreciate if a moderator could move it if possible.

Im a 4th year medical student from Sweden currently working on my master thesis. As part of this I have to make a large extraction of patient data from an ICU database and I'm having big issues with this. I should note that before 1 month ago i had never once seen any QlikView code, so bear with me if you will.

The first set of code is used to extract basic patient data from our database. The second set of code is used to extract certain lab data from said patients, from the same database.

Our issue is this. In the first code, we are only interested in, and only want to extract information on patients with VDranIn and VDranUt. (I.e, there are patients in the database who don't have VDranIn and VDranUt, we don't want those) However, we get duplicates of lab results on patients who have more than one VDranIn and VDranUt.

Simplified it basically looks like this.

PatientID VDranIn LabResult SampleTime

1 2016-06-06 1 2016-06-06 10:45

1 2016-06-06 2 2016-06-08 12:00

1 2016-06-06 3 2016-06-12 13:00

1 2016-06-10 1 2016-06-06 10:45

1 2016-06-10 2 2016-06-08 12:00

1 2016-06-10 3 2016-06-12 13:00

As you can see, we get repeated sequences of lab results for each VDranIn, even though it's the same Patient.

I've tried using:

min(pcs.StartTime) as VDranIn,

max(pcs.EndTime) as VDranUt,

In order to only get one VDranIn, but it didn't work. No error, but we still got several VDranIn if more than one existed.

pcs.StartTime and pcs.EndTime are represented as YYYY-MM-DD HH:MM in their table.

Any help with this would be greatly appreciated. And of course, if I've left out any vital information in order to solve this, I'm happy to provide it. It's likely I've included a great deal of redundant code, but I'm very new to this so I don't really know what's important yet. My thesis is due January and this part is a tremendous bottle neck.

**1**

Temp_Data_Patient_E:
LOAD DISTINCT
PatientID,
SocSecurity,
InNIVA,
UtNIVA,
Date(Birthdate) as Födelsedatum,
ForeName,
Sex,
SurName,
WardID,
VDranIn,
VDranUt,
ICUDiagnosis;
SQL SELECT DISTINCT
     AdmissionTime,
      Birthdate,
      ForeName,
      dbs.PatientID,
      Sex,
      SocSecurity,
      SurName,
      pcs.StartTime as VDranIn,
      pcs.EndTime as VDranUt,
      dbs.WardID,
      ICUDiagnosis,
      min(dbs.StartTime) as InNIVA,
      max(dbs.EndTime) as UtNIVA
FROM dbo.P_GeneralData pgd
JOIN Department.dbo.D_BedStatus dbs
ON dbs.PatientID=pgd.PatientID
JOIN dbo.P_DischargeData pdd
ON pgd.PatientID=pdd.PatientID
JOIN Patient.dbo. P_PatCareSeqs pcs
ON pgd.PatientID=pcs.PatientID
AND SequenceID IN (343, 348, 888)
AND AdmissionTime >= '$(vAdmStartTime)' AND AdmissionTime <= '$(vAdmStopTime)' AND dbs.WardID = $(vWardID)
GROUP BY pcs.StartTime, pcs.EndTime, SocSecurity, dbs.PatientID, Sex, SocSecurity, dbs.WardID, AdmissionTime,      SurName, ForeName, ICUDiagnosis, dbs.WardID;

STORE Temp_InskrData_Patient_E into $(vPathName)Inf_InskrData_Patient_$(vQVDDatum)_E.qvd;

****************************************************************************************

**2**

InfectLab:
LOAD
PatientID,
VariableID,
Value,
SampleTime;
SQL SELECT
     pgd.PatientID,
      WardID,
     AdmissionTime,
      plr.VariableID,
      plr.Value,

plr.SampleTime
FROM Patient.dbo. P_GeneralData pgd
JOIN Patient.dbo. P_LabRes plr
ON pgd.PatientID = plr.PatientID
AND plr.VariableID IN (24000664, 24000665, 24000681, 24000682, 24000683)
AND AdmissionTime >= '$(vAdmStartTime)' AND AdmissionTime <= '$(vAdmStopTime)';

STORE InfectLab into $(vPathName)InfectLab$(vQVDDatum).qvd;

best regards,

Marcus

Peter_Cammaert · ‎2017-11-13

I'm not sure that your post supplies all the required information to get you what you want, but this may be a start. Assuming that your problem is situated in the first table, and that for every distinct PatientID there is only one WardID and one ICUDiagnosis (unlikely?), you can add the following code between the LOAD+SELECT of the first table, and the actual STORE:

Temp_InskrPatient_Data_E: // This table will be stored. See STORE statement...

NOCONCATENATE

LOAD PatientID,

Date(Min(Date#(pcs.StartTime, 'YYYY-MM-DD HH:MM'))) AS VDranIn,

Date(Max(Date#(pcs.EndTime, 'YYYY-MM-DD HH:MM'))) AS VDranUt

RESIDENT Temp_Patient_Data_E

GROUP BY PatientID;

LEFT JOIN(Temp_InskrPatient_Data_E)

LOAD DISTINCT

     PatientID,
     SocSecurity,
     InNIVA,
     UtNIVA,
     Födelsedatum,
     ForeName,
     Sex,
     SurName,
     WardID,
     ICUDiagnosis

RESIDENT Temp_Patient_Data_E;

DROP Table Temp_Patient_Data_E;

If WardID and ICUDiagnosis are not unique per PatientID, you first need to decide which values of those fields will be kept with VDranIn and VDranUt.

Best,

Peter

Anonymous · ‎2017-11-14

Hi Peter,

Thanks for a quick reply. WardID and ICUDiagnosis will indeed be unique per patient since we're only looking at patients admitted to a certain ICU and ICUDiagnosis is their primary diagnosis at admission. I will try this as soon as I can and see if it works

Marcus

Anonymous · ‎2017-11-14

Hi Peter,

We just tried your code and it's not working. We're getting the error: <pcs.StartTime> cannot be found. Not sure why this is. Is there a reason why

Date(Min(Date#(pcs.StartTime, 'YYYY-MM-DD HH:MM'))) AS VDranIn,

Date(Max(Date#(pcs.EndTime, 'YYYY-MM-DD HH:MM'))) AS VDranUt

the above statement cannot just be added into the original code? Almost like min(dbs.StartTime) and max(dbs.EndTime).

/Marcus