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
