JOIN after an aggregation - plz help

[datanibbler]

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

[datanibbler · Accepted solution]

OK,

I got it. The issue was the format: In my main table, the date and time are not numeric, but - well, a date and a time 😉 For some reason, in my aggregated table, the outcome was numeric - I just inserted a num() function in that last LOAD and now it works.