2 Replies Latest reply: Aug 8, 2013 1:48 PM by Jamie Hicks RSS

    Previous() function equivalent in Expressor

    Jamie Hicks

      I'm trying to match some functionality that is in QlikView and I'm not sure how to go about it. I don't see any native Expressor function that does what I am looking for, so I'm pretty sure this will require datascript...but I don't see datascript functions that do what I am looking for either.

       

      Here is a portion of a QlikView Load Script that uses the previous() and peek() function to sequence values in a table:

      LOAD
      NNCoid,
      NNID,
      NNFacility,
      IF(NNCoid=Previous(NNCoid),Peek('Rank')+1,1) AS Rank

       

      Basically, the code says: if the NNCoid value from the previous row is  = the NNCoid value on the current row, then add 1 to the value in the Rank field, otherwise use the value of 1. The result is a sequential numbering that starts over at each change of NNCoid.

       

      If I were to write this in SQL, I would Rank() OVER(PARTITION BY NNCoid ORDER BY NNCoid,NNID)

       

      I'm extremely new to LUA so any assitance would be greatly appreciated.

       

      Thanks,

       

      Jamie Hicks

        • Re: Previous() function equivalent in Expressor

          Yes, this will require some scripting.  I'll discuss with respect to putting the code into a Transform operator, but if this is something you do frequently, you might try developing a generic version in a datascript module.

           

          What you will need to do is save the NNCoid value of each record as it passes through a Transform so that you can compare to the next record.

           

          • Use a function rule in a Transform operator.
          • In the initialize function, set the value of a variable, e.g., NNCoid, to a starting value that will be different from any possible incoming NNCoid value.
          • In the transform function, compare the value of NNCoid in the current record to the value in the variable.
          • If they are equal, adjust the value of Rank accordingly.
          • If they are unequal, set the value of Rank accordingly.
          • Update the value held in the variable NNCoid.

           

          Something like

           

           

          NNCoid = 0  -- not strictly required

          function initialize()

            NNCoid = 0

          end

           

          -- the only input parameter is Rank

          -- the only output parameter is Rank

          -- all other values are passed across the rule by default

          function transform(input)

            output = {}

            if input.NNCoid==NNCoid then

              Rank = input.Rank + 1

            else

              Rank = 1

            end

           

            NNCoid = input.NNCoid

            output.Rank = Rank

            return output

          end