topic Re: Show only unique based on criteria in QlikView

Show only unique based on criteria

Thu, 27 Feb 2014 08:38:02 GMT

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.

Re: Show only unique based on criteria

Thu, 27 Feb 2014 08:44:57 GMT

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

Re: Show only unique based on criteria

its_anandrjs — Thu, 27 Feb 2014 08:45:29 GMT

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

Re: Show only unique based on criteria

Thu, 27 Feb 2014 08:45:39 GMT

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

Re: Show only unique based on criteria

Thu, 27 Feb 2014 08:55:48 GMT

Hi Aamir,

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

Thanks again.

Re: Show only unique based on criteria

Thu, 27 Feb 2014 08:59:38 GMT

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.

Re: Show only unique based on criteria

Thu, 27 Feb 2014 09:07:14 GMT

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.

Re: Show only unique based on criteria

its_anandrjs — Thu, 27 Feb 2014 09:11:22 GMT

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

Re: Show only unique based on criteria

its_anandrjs — Thu, 27 Feb 2014 09:14:51 GMT

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

Re: Show only unique based on criteria

Thu, 27 Feb 2014 09:37:15 GMT

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];

Re: Show only unique based on criteria

Thu, 27 Feb 2014 23:36:11 GMT

Hi Amir,

Thanks for your post. I tried but getting "invalid expression..." message.

Re: Show only unique based on criteria

Thu, 27 Feb 2014 23:40:51 GMT

Hi Anand,

I used the exact script you posted and it gave me error message: "Execution of script failed....".