topic Re: Show only distinct time values in list box in QlikView

Show only distinct time values in list box

Mon, 17 Sep 2012 11:51:49 GMT

Hi,

I am having a date field named 'CalenderDate' which is in 'DD/MM/YYYY HH:MM:SSS' format. I Want to only show 'HH' part of it in list box. The data is similar to below

01/01/2012 10:15:123

01/01/2012 10:16:876

01/01/2012 23:11:111

so there are multiple records for one date each having different time stamp.

To show only 'HH' part in list box, I am using formula as =Date(CalenderDate, 'HH); it is returning HH part, but it is duplicating.

As you can see there are two records having time stamp 10:15 and 10:16; so it is showing 10 two times in list box.

I want to display it only once. Does anyone knows how to achive this?

Thanks in advance.

Raj Patil

Re: Show only distinct time values in list box

kamalqlik — Mon, 17 Sep 2012 13:17:48 GMT

Hi Friend,

Just use the expression date(floor(num(Date)),'HH') as Hour1.

I think it will sort your problem and will give distinct record in base of hours.

Regards

Kamal

Re: Show only distinct time values in list box

Mon, 17 Sep 2012 16:11:42 GMT

It is showing duplicates because it still sees a distinct serial number, just in a different format. If you click one of the 10's, you will notice that it likely returns only that individual record. To get around this, I recommend using a Master Calendar. You can add an Hour field in the script.

http://community.qlik.com/thread/48693

Re: Show only distinct time values in list box

gouthamkk — Tue, 17 May 2016 18:29:08 GMT

HI,

I came across same issue and i know it is too late but i tried below expression in the script and got the result

Num#(Date(CalenderDate, 'HH)).

Hope this works for you too

Re: Show only distinct time values in list box

swuehl — Tue, 17 May 2016 18:31:04 GMT

use

Hour(CalenderDate) As Hour

=Hour(CalendarDate)

e.g. in a list box field expression.

Re: Show only distinct time values in list box

engishfaque — Tue, 17 May 2016 18:40:59 GMT

Dear talktorajpatil ,

Here it is,

=Date(Floor(CalenderDate), 'HH)

Kind regards,

Ishfaque Ahmed

Re: Show only distinct time values in list box

MarcoWedel — Tue, 17 May 2016 20:22:48 GMT

Num(Hour(CalenderDate),'00')

to always get two digits

hope this helps

regards

Marco

Re: Show only distinct time values in list box

sunny_talwar — Tue, 17 May 2016 20:23:59 GMT

Guys this is a discussion from 2012

Re: Show only distinct time values in list box

swuehl — Tue, 17 May 2016 20:26:43 GMT

No, this is a current discussion, only the start is four years old

Re: Show only distinct time values in list box

MarcoWedel — Tue, 17 May 2016 20:27:14 GMT

didn't notice ...

Re: Show only distinct time values in list box

MarcoWedel — Tue, 17 May 2016 20:31:28 GMT

3 posts dating from 2012,

7 from 2016

(8 actually if I hit "Add reply")

I guess it's ok now to continue this thread ...

Re: Show only distinct time values in list box

sunny_talwar — Tue, 17 May 2016 20:33:14 GMT

Math works, but the only thing to keep in mind is that we probably won't hear back from the OP.

p.s. it would be funny if he comes back and mark one of the new responses as correct