7 Replies Latest reply: Oct 23, 2017 4:50 AM by Nicolò Cogno RSS

    Find last occurrence of value in script

    Nicolò Cogno

      Hi everybody,

       

      I saw this example at FieldIndex - script and chart function ‒ Qlik Sense :

       

      Names:

      LOAD * inline [

      "First name"|"Last name"|Initials|"Has cellphone"

      John|Anderson|JA|Yes                              // #1

      Sue|Brown|SB|Yes                              // #2

      Mark|Carr|MC |No                              // #3

      Peter|Devonshire|PD|No                              // #4

      Jane|Elliot|JE|Yes                              // #5

      Peter|Franc|PF|Yes ] (delimiter is '|');                              // #6

       

      John1:

      Load FieldIndex('First name','John') as MyJohnPos    //This gives 1

      Resident Names;

       

      Peter1:

      Load FieldIndex('First name','Peter') as MyPeterPos   //This gives 4

      Resident Names;

       

      My question is the following: is there a way to find the index (or directly find the value) of the last occurrence of the name? What I'd like to say is wheter there's a way to have MyPeterPos = 6 instead of 4.

       

      Thanks a lot in advance,

       

      best regards,

       

      Nicolò Cogno

        • Re: Find last occurrence of value in script
          Clever Anjos

          Please try this

          Names:

          LOAD *,RecNo() as index inline [

          "First name"|"Last name"|Initials|"Has cellphone"

          John|Anderson|JA|Yes                              // #1

          Sue|Brown|SB|Yes                              // #2

          Mark|Carr|MC |No                              // #3

          Peter|Devonshire|PD|No                              // #4

          Jane|Elliot|JE|Yes                              // #5

          Peter|Franc|PF|Yes ] (delimiter is '|');                              // #6

           

          John1:

          Load FieldIndex('First name','John') as MyJohnPos    //This gives 1

          Resident Names;

           

          Peter1:

          Load FieldIndex('First name','Peter') as MyPeterPos   //This gives 4

          Resident Names;

           

           

          load "First name", max(index)  as maxindex Resident Names Group by "First name";