Show only distinct time values in list box

[#1]

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

[kamalqlik]

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

[#3]

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

[gouthamkk]

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

[swuehl]

use

Hour(CalenderDate) As Hour

or

=Hour(CalendarDate)

e.g. in a list box field expression.

[engishfaque]

Dear talktorajpatil ,

Here it is,

=Date(Floor(CalenderDate), 'HH)

Kind regards,

Ishfaque Ahmed

[MarcoWedel]

or

Num(Hour(CalenderDate),'00')

to always get two digits

hope this helps

regards

Marco

[sunny_talwar]

Guys this is a discussion from 2012

[swuehl]

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

[MarcoWedel]

didn't notice ...