6 Replies Latest reply: Oct 4, 2017 9:31 AM by Rob Wunderlich 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";

            • Re: Find last occurrence of value in script
              Nicolò Cogno

              Hi Clever and thanks for your reply,

               

              that was just an example, I've got a bigger algorithm that load rows from a huge table and I'd like to find a function (or a composition of functions) to perform that task. Do you know if exist something like that?

               

              Thanks again,

               

              best regards,

               

              Nicolò Cogno