Load distinct record

[#1]

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?

[#2]

try with:

load userId,

        lastvalue(ip),

        max(time)

from table group by userId;

Hope this help you.

[#3]

HI,PLEASE FIND THE ATTACHED QVW.

[#4]

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

[#5]

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

[#6 · Accepted solution]

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

[SunilChauhan]

take a pivot table

in dimension

user ID

Ip

and in expression

Maxstring(time)

hope this helps

[#8]

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