Distinct hours:Mins

[jagannalla]

Hello,

i've date field like shown below:

DateColumn

2012-07-03 15:51:53.58

2012-07-03 15:52:21.74

2012-07-03 15:52:49.89

2012-07-03 15:53:18.54

2012-07-03 15:53:46.82

- I want to retrive the distinct dates and time. I can able to do for dates. But i can't do for time how can i achive this. For date i'm using

Date(Floor(DateColumn),'MM/DD/YYYY') as Date

I WANT DISTICNT TIMES

for example

15:51

15:52

15:53

[jvitantonio]

Hi, try this in your load script:

TIME:

LOAD

Timestamp(DATE) AS DATE,

hour(DATE) as Hour, Minute(DATE) as Min

INLINE [

DATE

2012-07-03 15:51:53.58

2012-07-03 15:52:21.74

2012-07-03 15:52:49.89

2012-07-03 15:53:18.54

2012-07-03 15:53:46.82

];

HOUR:

LOAD Distinct

MakeTime(Hour,Min) as Hour1

Resident TIME;

[vincent_ardiet]

Hi,

You can use the TIME function like this : Time(DateColumn,'hh:mm')

Regards,

Vincent

[jagannalla]

Ya i already tried with time function but it doen't give me distinct times.

QlikUser14.. I don't want the time in AM/PM. I need in 24 hours format only as what i've in my timestamp.

[jvitantonio]

That's formatted in your script:

SET TimeFormat='hh:mm:ss TT';

[vincent_ardiet]

You're right, QV doesn't make the distinct, perhaps it's due to the dual value.

But, like this it works : Time#(Time(DateColumn,'hh:mm'))

Regards,

Vincent

[nagaiank]

The following script works and gives distinct hh:mm

TIME:

LOAD Distinct Text(TimeStamp(DATE,'hh:mm')) as Time;

LOAD * INLINE [

DATE

2012-07-03 15:51:53.58

2012-07-03 15:52:21.74

2012-07-03 15:52:49.89

2012-07-03 15:53:18.54

2012-07-03 15:53:46.82

];

[jagannalla]

Thanks to all.

[hic]

The simplest way to do this is to use the Frac() function, which just cuts of the integer part of a number, and then format it as time. Hence:

Time(Frac(Date))

HIC

[jagannalla]

Henric Cronstrom,

But i need to distinct hours, mins time. I don't want seconds? Please see the image.