topic Re: Problem aggregating a table and joining in QlikView

Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 11:16:14 GMT

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

Re: Problem aggregating a table and joining

rubenmarin — Wed, 26 Nov 2014 11:35:08 GMT

Hi DataNibbler,

I upload a sample you can use to join with Transaction_aux only by TRACKING_NUMBER

Hope this helps!

Re: Problem aggregating a table and joining

Gysbert_Wassenaar — Wed, 26 Nov 2014 11:41:05 GMT

I'm not quite sure what you're after. Maybe this helps:

Data:
LOAD TRACKING_NUMBER,
     TRAN_DATE,
     USER
FROM [comm143662.xlsx] (ooxml, embedded labels, table is Sheet1);

join(Data)
LOAD TRACKING_NUMBER, max(TRAN_DATE,2) as TRAN_DATE, 1 as SecondToLastFlag
Resident Data
GROUP BY TRACKING_NUMBER;

If you want the user name instead you could use this:

Users:
LOAD TRACKING_NUMBER, FirstSortedValue(USER,-TRAN_DATE,2) as USER_OF_INTEREST
FROM [comm143662.xlsx] (ooxml, embedded labels, table is Sheet1)
GROUP BY TRACKING_NUMBER;

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 12:28:11 GMT

Hi Ruben,

that seems worth a try. I hadn't thought of using this function. The effect should be basically the same as by aggregating with max().

Let's see.

Thanks a lot!

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 12:36:39 GMT

Hi Gysbert,

well, what I actually want is two things:

- The (date-time-stamp of the) very last booking out of that list of bookings I get from our database

- The user who effected it

Any way, I have to aggregate the table - whether I go for the last user first and then join the date-time-stamp or the other way round doesn't really matter.

In the output table I want only one date-time-stamp per box_ID and one user to match it.

That's it basically.

I'll try to find out first whether my problem is in the aggregation or in the JOIN and then I'll try Rubben's suggestion.

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 12:43:00 GMT

Hi all,

well, I have just tested and positively confirmed that the aggregation works as expected - right after that step, I have only one date-time-stamp (in numeric format) per box.

=> The problem must be in the join. I'm not sure what can be wrong about that.

Unfortunately, it takes a few minutes each time I run that script. I'll try again. Maybe I have to explicitly turn both date-time-stamps into numeric format?

Re: Problem aggregating a table and joining

Gysbert_Wassenaar — Wed, 26 Nov 2014 12:43:05 GMT

Ok, both the timestamp and the user.

Last entry per tracking number:

LOAD
     TRACKING_NUMBER,
     max(TRAN_DATE) as LAST_TRAN_DATE
     FirstSortedValue(USER,-TRAN_DATE) as USER_OF_LAST_TRAN_DATE
FROM .....
GROUP BY TRACKING_NUMBER;

Or if you actually want the second last entry per tracking number:

LOAD
     TRACKING_NUMBER,
     max(TRAN_DATE,2) as LAST_TRAN_DATE
     FirstSortedValue(USER,-TRAN_DATE,2) as USER_OF_LAST_TRAN_DATE
FROM .....
GROUP BY TRACKING_NUMBER;

If you don't care about the tracking number, but want the absolute (second to) last entry then remove tracking number from the load statement and remove the group by too.

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 12:53:07 GMT

Great!

I would still like to know why the JOIN doesn't work, but your suggestion with FIRSTSORTEDVALUE() looks very good.

I need all three informations: The date-time-stamp, the TRACKING_NUMBER (box_ID) and the user.

Thanks a lot!

Best regards,

DataNibbler

Re: Re: Problem aggregating a table and joining

rubenmarin — Wed, 26 Nov 2014 12:56:19 GMT

I made another sample joining and simplier, but it's the same Gysbert has posted, anyway i upload it as qvw.

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 13:12:07 GMT

Now I have another strange problem.

With this, I get only one booking with the date-time-stamp, that is ok.

In some cases, I also get exactly one name, the user who did the booking.

<=> In other cases, no name is available. That is very strange for I don't think it is possible to log on to our database at all to do any booking there without the logon being registered.

I have to check that.

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 13:24:22 GMT

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?

Re: Problem aggregating a table and joining

Gysbert_Wassenaar — Wed, 26 Nov 2014 13:45:41 GMT

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.

Re: Problem aggregating a table and joining

rubenmarin — Wed, 26 Nov 2014 13:53:42 GMT

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.

Re: Problem aggregating a table and joining

datanibbler — Wed, 26 Nov 2014 13:58:21 GMT

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!