3 Replies Latest reply: Jul 18, 2017 9:52 AM by Kelly McGrady RSS

    Last function

    ibrahibra aljefri

      hi

      i am using qlik sense desktop and i want to take last record for each employee of a created field which it is datatype is  Date and time

      When i use below function it gives me wrong expression

       

      LastChannel:

      Right Join

      LOAD LastValue(Created),

          "Created By",

          "Created By Name",

          Channel;

      SQL SELECT Created,

          "Created By",

          "Created By Name",

          Channel

      FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

       

      regards

        • Re: Last function
          Martin Pohl

          Aggregation function (like lastvalue) requires a group by statement

           

          LastChannel:

          Right Join

          LOAD LastValue(Created),

              "Created By",

              "Created By Name",

              Channel

          Group by "Created By", "Created By Name", Channel

          ;

          SQL SELECT Created,

              "Created By",

              "Created By Name",

              Channel

          FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

           

          Maybe Creaters are different so you will get the last value for all creaters.

           

          So load in two steps:

           

          TempSql:

          SQL select as below;

           

          left join

          load

               lastvalue(Created) as LastCreation,

          Channel

          Group by Channel;

          resident Temp;

           

          Data:

          load

          "Created By",

          "Created By Name",

          Channel,

          LastCreation as Creation

          resident Temp

          where Creation = LastCreation;

          drop table Temp;

           

          Regards

          • Re: Last function
            Jaume Criballés Faja

            I think that you need to use a GROUP BY here. Something like this:

             

            LastChannel:

            Right Join

            LOAD LastValue(Created),

                "Created By",

                "Created By Name",

                Channel

            Group By "Created By",    "Created By Name",    Channel;

            SQL SELECT Created,

                "Created By",

                "Created By Name",

                Channel

            FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

            • Re: Last function
              Kelly McGrady

              LastChannel:

              LOAD First 1

                  "Created By",

                  "Created By Name",

                  Channel;

              FROM "D:\ALL EMPLOYESS & DOWN PAYMENT\New Microsoft Access Database.accdb".DATAModified;

              Order by desc;

              Flip the order of the list and then take the first row which will always get you the last entry in the column even when you update.