Show only unique based on criteria

[#1]

Hi guys,

I have loaded table files (CSV) into qlikview. There are fields in the table, CARD USERNAME and DATE & TIME. The records in this table represent the user (CARD USERNAME) who tapped their passes into the door entrance system and the date time stamp (DATE & TIME). So there can be several instances of CARD USERNAME in the table, each wil just have different DATE & TIME.

By the way, I am using personal edition.

Below is my code

LOAD Sequence,

   [Date and Time],

   [Card user name],

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

When I create tablebox and added all fields, it simply list all instances of CARD USERNAME and DATE & TIME.

Question;

What do Ineed to do so that the table will only show one instance CARD USERNAME with the corresponding DATE & TIME and the criteria being that the DATE & TIME to be used is the latest one.

Thanks in advance.

[#2]

use first sort value function: it gives value corresponding to minimum number, in this case it is date and time. For max use -datetime

[its_anandrjs]

Hi,

I am not understanding clearly can you please provide me any sample file. As i understand you want one field based on two field so use concat command for that.

HTH

Thanks & Regards

[#4 · Accepted solution]

Jim,

try another object like a pivot or straight table (chart / in general tab choose one of the two)

Dimension: Card user name

Expression: max(date & time)

Fabrice

[#5]

Hi Aamir,

How exactly should I do your suggestion? I am very new to qlikview and now sure how to proceed.

Thanks again.

[#6]

Hi Anand,

Currently my table box would show:

CARD USERNAME   DATE & TIME

jimlim              2/27/2014 7:04:52 am

jimlim              2/27/2014 8:06:33 am

jimlim              2/27/2014 10:04:11 am

I would like that it only shows

CARD USERNAME   DATE& TIME

jimlim              2/27/2014  10:04:11 am

So only 1 jimlim (unique) and the DATE & TIME is 2/27/2014 10:04:11 am, which is the latest.

Will you kindly provide details on how do I use CONCAT fuction to achieve this?

Thanks in advance.

[#7]

Thanks Fabrice, I did some clicking and somehow I figured out what you suggested. And it is exactly what I needed.

Thanks again and have a nice day.

[its_anandrjs]

Hi,

If say in expression level so use Expression like Max(DATE& TIME) and in dimension use CARD USERNAME by using this only latest value is shown.

HTH

Thanks & Regards

[its_anandrjs]

Hi,

If you want to do this on the load script do like this

LOAD Sequence,

   [Date and Time],

   [Card user name],

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where [Date and Time] = Max([Date and Time]) ;

HTH

Thanks & Regards

[#10]

Alternatively you can use

LOAD Sequence,

   max([Date and Time]) as [Date and Time],

   [Card user name],

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

group by  [Card user name];