1 Reply Latest reply: Sep 5, 2016 7:49 AM by Friedrich Hofmann RSS

    JOIN after an aggregation - plz help

    Friedrich Hofmann

      Hi,

       

      in the script I'm currently working on, there is one number (>>Serial_Lot<<) that all records have in common - but there are 43 different groups (>>Tracks<<) which are part of this >>Serial_Lot<<.

      => For every Track, there are a number of bookings and I'm interested in the last one - but they have all been appended into one long table, so I have to distinguish between the Tracks again.

       

      => I do a RESIDENT LOAD and load a small aggregated table where I have one record for every Track, and the exact date_and_time of the last booking. That works fine so far.

      <=> In this table, I need the type (>>TranCode<<) of that booking because I will have to continue processing my data accordingly.

      My last LOAD looks like this:

       

      Last_Bookings:
      LOAD
      TRACKING_NUMBER as Track_v2,
      max((TRAN_DATE_TD + TRAN_DATE_Time)) as Zeitstempel_letzte_Buchung
      RESIDENT Transaktionen_Archiv
      GROUP BY TRACKING_NUMBER
      ;

       

      Now I would like to add (join) the TRAN_CODE from that same base_table - the JOIN would have to be on both fields. So I would go like

       

      INNER JOIN (Last_Bookings)

      LOAD

      TRACKING_NUMBER as Track_v2,

      (TRAN_DATE_TD + TRAN_DATE_Time) as Zeitstempel_letzte_Buchung,

      TRAN_CODE as TranCode_letzte_Buchung

      RESIDENT Transaktionen_Archiv

      ;

       

      <=> Well, I already tried that, it didn't work. The JOIN was apparently done on only the Track_Number, I got 14 records instead of 2.

      Can anyone spot the mistake?

      Thanks a lot!

      Best regards,

       

      DataNibbler