Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
URL | TimeStamp |
---|---|
10:30 | |
10:31 | |
10:32 | |
10:33 |
Hi,
is it the option to use order by clause in your load statement, as order by URL, TimeStamp DESC ? That way you can use lookup statement to get correct value.
regards
Thanks,
This brings up the timestamp 10:33 or 10:30 depending on the the sort order. This is because it is always looking for the first match and ignores the rest of the matches.
It could work if it was possible get QV to apply the function after each row is loaded instead of over the whole table. Is this possible?
With lookup in load script you can experiment by yourself, here is example how to get max timestamp with max statement (MAX_TIME column):
VALUES1:
LOAD * INLINE [
URL1, TimeStamp1
Facebook, 10:20
Facebook, 10:30
Google, 11:00
];
VALUES:
LOAD URL1 as URL, Timestamp(TimeStamp1, 'hh:mm') as TimeStamp Resident VALUES1 order by URL1, TimeStamp1 DESC;
drop Table VALUES1;
inner join (VALUES)
load URL, Timestamp(Max(TimeStamp), 'hh:mm') as MAX_TIME Resident VALUES Group By URL;
Many thanks for your help guys, but not quite what i need. I need to reference the last timeStamp previous that a URL was accessed
ID | URL | TimeStamp | referenced TimeStamp (calculated Column) |
---|---|---|---|
1 | 10:30 | nul | |
2 | 10:35 | nul | |
3 | 10:36 | 10:30 (from row 1) the most recent previous time facebook was accessed | |
4 | 10:40 | 10:35 (From row 2) the most recent previous time google was accessed | |
5 | 10:41 | 10.36 (from row 3) the most recent previous time google was accessed |
A lookup would work if i could change the Lookup to search up from the current record, instead of down from the top of the table
I dont know if this is possible or not, so please let me know if you think it may not be .
Thanks!
Sam
See attached example.
Lovely cheers.
I already have similar order by and previous solutions on my script i cant beleive i didnt think of that. It's going to get confusing though
thanks again