3 Replies Latest reply: Nov 14, 2017 1:01 AM by Marcus Bådholm RSS

    Beginner trying to make sense of this code

    Marcus Bådholm

      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


        • Re: Beginner trying to make sense of this code
          Peter Cammaert

          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