Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Show only distinct time values in list box

Hi,

I am having a date field named 'CalenderDate' which is in 'DD/MM/YYYY HH:MMSmiley FrustratedSS' 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

11 Replies
kamalqlik
Valued Contributor

Re: Show only distinct time values in list box

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

Not applicable

Re: Show only distinct time values in list box

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
Contributor

Re: Show only distinct time values in list box

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

MVP
MVP

Re: Show only distinct time values in list box

use

Hour(CalenderDate) As Hour

or

=Hour(CalendarDate)

e.g. in a list box field expression.

engishfaque
Valued Contributor III

Re: Show only distinct time values in list box

Dear talktorajpatil ,

Here it is,

=Date(Floor(CalenderDate), 'HH)

Kind regards,

Ishfaque Ahmed

Re: Show only distinct time values in list box

or

Num(Hour(CalenderDate),'00')

to always get two digits

hope this helps

regards

Marco

Re: Show only distinct time values in list box

Guys this is a discussion from 2012

MVP
MVP

Re: Show only distinct time values in list box

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

Re: Show only distinct time values in list box

didn't notice ...