Problem aggregating a table and joining

[datanibbler]

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

[datanibbler]

Just as I thought - the names are all in the database, but for some reason I cannot get them out.

So the method with FirstsortedValue() does not seem to work for me after all. What a pity. Now I have to try JOINing after all.

Up to now, the jOIN seems to have multiplied the records again - right after the aggregation, before the JOIN I had only one date-time-stamp and user per TRACK, after the JOIN I had many.

I thought QlikView always performs an INNER JOIN if not otherwise specified?

=> Then it should return only as many records as my primary table has, no? (in this case, the primary table would be the aggregated one, TD_aux, as I specify in the JOIN with the statement

>> JOIN (TD_aux) <<

Right?

[Gysbert_Wassenaar]

Firstsortedvalue will only work if exactly one value can be returned. If there are two records with exactly the same timestamp but with different users then the firstsortedvalue cannot return the user. It cannot know which of the two should be returned.

[rubenmarin]

Hi, FirstsortedValue() won't work if there two or more equal "first sort" values, the one's without name has the same TRAN_DATE that another? maybe you can try with:

Subfield(Concat(USER, ';', -TRAN_DATE), ';', 1) as Letzter_Bucher

I think default is Outer join, anyway I prefer to identify it as inner, left, right or outer and avoid defaults.

[datanibbler]

Hi Ruben,

default is OUTER? That would certainly explain a lot.

I specified the INNER JOIN now - and it works. Without any other changes to my method.

Thanks a lot!