2 Replies Latest reply: Mar 28, 2015 6:50 AM by Massimo Grossi RSS

    LastValue?

      I have a file I have uploaded into qlikview that can have multiple responses for the same element. I am looking for a way to identify the last entered value - I believe LASTVALUE may be able to identify this for me but I am having trouble with the syntax due to if statements that need to be built into my script as well (this is necessary because there are also multiple elements that need to be distinguished). Any help would be much appreciated it. Below I have included an example of the data (I only want to return the last line) as well as my script:

       

      Section NameDocumentation Date & TimeElement DescriptionResponse Value- Free text
      Pneumococcal and Influenza Evaluation03/20/2015 10:46:00 PM Pneumococcal Vaccine StatusReceived after age 65 years
      Pneumococcal and Influenza Evaluation03/20/2015 11:33:00 PM Pneumococcal Vaccine StatusNever received pneumococcal vaccine
      Pneumococcal and Influenza Evaluation03/21/2015 12:43:00 AM Pneumococcal Vaccine StatusNever received pneumococcal vaccine
      Pneumococcal and Influenza Evaluation03/21/2015 02:44:00 AM Pneumococcal Vaccine StatusReceived after age 65 years
      Pneumococcal and Influenza Evaluation03/21/2015 02:50:00 AM Pneumococcal Vaccine StatusNever received pneumococcal vaccine
      Pneumococcal and Influenza Evaluation03/21/2015 03:08:00 AM Pneumococcal Vaccine StatusReceived after age 65 years
      Pneumococcal and Influenza Evaluation03/21/2015 08:29:00 AM Pneumococcal Vaccine StatusReceived after age 65 years
      Pneumococcal and Influenza Evaluation03/25/2015 09:34:00 PM Pneumococcal Vaccine StatusReceived after age 65 years
      Pneumococcal and Influenza Evaluation03/26/2015 04:19:00 PM Pneumococcal Vaccine StatusReceived after age 65 years

       

      Documentation:
      LOAD [Financial Number] as FIN,
      [Admit Date & Time] as AdmitDate,
      [Person Name- Full] as PatientName,
      [Person Location- Facility (Curr)] as Facility,
      [Person Location- Nurse Unit (Curr)] as Unit,
      //[Form Description],
      //[Section Name],
      [Documentation Date & Time] as Completed,
      //[Element Description],
      //[Response Value- Free text]
      if([Element Description]='Influenza Vaccine Status'
      ,
      [Response Value- Free text]) as FluVaccineStatus,
      if([Element Description]='Pneumococcal Vaccine Status'
      ,
      [Response Value- Free text]) as PnVaccineStatus,
      if([Element Description]='Reg PN Influenza Eligibility'
      ,
      [Response Value- Free text]) as FluEligibility,
      if([Element Description]='Reg PN Influenza Not Given Reason vB'
      ,
      [Response Value- Free text]) as FluContraindications,
      if([Element Description]='Reg PN Influenza Vaccine Refusal vA'
      ,
      [Response Value- Free text]) as FluVaccineRefusal,
      if([Element Description]='Reg PN Pneumo Contraindication Reason vD'
      ,
      [Response Value- Free text]) as PnContraindications,
      if([Element Description]='Reg PN Pneumococcal Vaccine Refusal'
      ,
      [Response Value- Free text]) as PnVaccineRefusal,
      if([Element Description]='Reg PN Pneumonia Eligibility'
      ,
      [Response Value- Free text]) as PnVaccineEligibility
      FROM
      [Source\PN & Flu Eval Form Usage.xls]
      (
      biff, embedded labels, table is [Vaccine Form Documentation$]);

        • Re: LastValue?
          Ramon Covarrubias

          there is more than a couple of ways to do this though

           

          I am assuming that the data is in descending order

           

           

           

          LOAD [Section Name],

               LastValue([Documentation Date & Time]) as [Documentation Date & Time],

               [Element Description],

               LastValue([Response Value- Free text]) as [Response Value- Free text]

          FROM

          [C:\Users\rcovarrubias\Documents\Qlik\community\data.xlsx]

          (ooxml, embedded labels, table is Sheet1)

          group by [Section Name],[Element Description]

           

          or

           

          temp:

          LOAD [Section Name],

              [Documentation Date & Time] as [Documentation Date & Time],

               [Element Description],

               [Response Value- Free text] as [Response Value- Free text],

               RowNo() as Row

          FROM

          [C:\Users\rcovarrubias\Documents\Qlik\community\data.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          right join (temp)

          load

          max(Row) as Row,

          [Element Description],

          [Section Name]

          Resident temp

          group by [Element Description],

          [Section Name];

           

          would give you this result 

          Documentation Date & Time Element Description Response Value- Free text Section Name
          3/26/2015 4:19:00 PMPneumococcal Vaccine StatusReceived after age 65 yearsPneumococcal and Influenza Evaluation
          • Re: LastValue?
            Massimo Grossi

            SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

             

            source:          // replace with load from your excel

            LOAD [Section Name],

                 [Documentation Date & Time],

                 [Element Description],

                 [Response Value- Free text]

            FROM

            [http://community.qlik.com/thread/157948]

            (html, codepage is 1252, embedded labels, table is @1);

             

            final:

            load *,

            if([Section Name]<>Peek([Section Name]),1,0) as LastFlag

            resident source

            order by [Documentation Date & Time] desc

            ;

             

            DROP Table source;

             

            1.png