Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Advanced Lookup Help

Hi Guys,
Ive been asked to do an internet usage report using our teams proxy data . As you might imagine the data is quite diffucult to work with.
Im stuck trying to do an advanced lookup
Effectively i need to find the timestamp of the last time a URL was accessed. I have the following data
URLTimeStamp
Facebook10:30
Facebook10:31
Google10:32
Facebook10:33
When I am loading in the 4th row for Facebook i need to bring the most recent time it was accessed to do a TimeSpent column. So i need to bring up the TimeStamp column  from the 2nd row to do a comparison. Is this possible? I dont think the lookup function will work because it says it looks for the first time the data appears so it will always bring up the 1st row in the table.
Any suggestions appreciated Many thanks

Sam
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Advanced Lookup Help

See attached example.


talk is cheap, supply exceeds demand
6 Replies
Not applicable

Re: Advanced Lookup Help

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

Not applicable

Re: Advanced Lookup Help

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?

Not applicable

Re: Advanced Lookup Help

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;

Not applicable

Re: Advanced Lookup Help

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

IDURLTimeStampreferenced TimeStamp (calculated Column)
1Facebook10:30nul
2Google10:35nul
3Facebook

10:36

10:30 (from row 1) the most recent previous time facebook was accessed
4Google10:40

10:35 (From row  2) the most recent previous time google was accessed

5Facebook10: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

MVP & Luminary
MVP & Luminary

Re: Advanced Lookup Help

See attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Advanced Lookup Help

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