I upload a sample you can use to join with Transaction_aux only by TRACKING_NUMBER
Hope this helps!
test2.qvw 149.8 K
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;
comm143662.qvw 150.8 K
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.
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?
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.
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.
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) <<
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.