13 Replies Latest reply: Nov 26, 2014 8:58 AM by Friedrich Hofmann RSS

    Problem aggregating a table and joining

    Friedrich Hofmann


      Hi,

       

      I have the following scenario:

      - From our database I get a list of bookings, each with an exact date-time-stamp and the signon of the user. I will attach a small sample file to illustrate what I have

      - I want the last (actually the second-to-last) booking of each individual box - the others are not relevant.

      - To this end, I aggregate and I use the max() function to get the last entry, like this:

       

      Noconcatenate

      Transaction_aux:
      LOAD
         
      TRACKING_NUMBER as Track_AOS,
         
      max(TRAN_DATE) as Letzte_Buchung
      RESIDENT Transaction_Details_pre
      GROUP BY TRACKING_NUMBER;

      (I  cannot include the user here, otherwise I'd only get the last posting grouped by user, but I want the absolute last booking of that box)

       

      The next step is to join the user from the original table via the TRACKING_NUMBER and the TRAN_DATE (which, together, form a unique key to every booking)

       

      JOIN (Transaction_aux)
      LOAD
         
      TRACKING_NUMBER as Track_AOS,
         
      TRAN_DATE as Letzte_Buchung,
         
      USER_SIGNO as Letzter_Bucher
      RESIDENT Transaction_Details_pre;
      DROP TABLE Transaction_Details_pre;

       

      <=> For some reason, I still have multiple bookings - and thus multiple users - in the resulting table though the date-time-stamp is not the same.

            Needless to say, I cannot use that - I need exactly one booking per TRACKING_NUMBER so I can then join with the names of the employees who were on duty
            at that time.

       

      Can anyone help me here?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler