topic Load distinct record in QlikView

Load distinct record

Wed, 02 May 2012 03:51:33 GMT

Hi, I am now having a table containing three fields like the following.

userId	ip	time
1234	192.188.1.1	2012-02-02
1234	192.234.1.1	2012-03-01
2345	192.345.3.2	2012-02-02
2345	188.134.2.3	2012-04-09

What I would like to do is to load this table with only the latest ip address for a particular user. After loading, the table should look like something like below.

userId	ip	time
1234	192.234.1.1	2012-03-01
2345	188.134.2.3	2012-04-09

Initially, I was trying to user aggregation function. What i did is

load userId,

ip,

max(time)

from table group by userId.

But it doesn't work. Could anyone help me on this?

Load distinct record

Wed, 02 May 2012 04:47:00 GMT

try with:

load userId,

lastvalue(ip),

max(time)

from table group by userId;

Hope this help you.

Re: Load distinct record

Wed, 02 May 2012 04:52:15 GMT

HI,PLEASE FIND THE ATTACHED QVW.

Re: Load distinct record

Wed, 02 May 2012 05:41:32 GMT

Hi theFourth,

Your solution gives me some hint. But it assumes that the the records are ordered by time right? I tried to change the order of the records and your solution fails. Any way to order the time after a group statement?

Regards,

Xue Bin

Load distinct record

Wed, 02 May 2012 05:44:13 GMT

Sorry Jatin,

The previous reply was meant for theFourth. I wrote the name wrongly. Could you post your code here as I have a qlikview personal edition and am unable to open your document. Thanks:)

Regards,

Xue Bin

Re: Load distinct record

Wed, 02 May 2012 05:55:55 GMT

Hi, I found the answer. The script i use is:

table:

load userId,

ip,

time

from xxx;

load userId,

lastValue(ip),

max(time)

resident table group by userId order by time;

drop table table;

By using a resident table, I am able to order the time first before i retrieve the last loaded record. Thanks all for help:)

Regards,

Xue Bin

Load distinct record

SunilChauhan — Wed, 02 May 2012 05:57:05 GMT

take a pivot table

in dimension

user ID

and in expression

Maxstring(time)

hope this helps

Re: Load distinct record

Wed, 02 May 2012 06:04:10 GMT

Hi Sunil,

Thanks for your reply. What I was trying to do does not involve visualization. I just wanted to import from a file in specified requirement. Thanks:)

Regards,

Xue Bin