topic Re: Distinct hours:Mins in QlikView

Distinct hours:Mins

jagannalla — Mon, 09 Jul 2012 09:27:49 GMT

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

Re: Distinct hours:Mins

jvitantonio — Mon, 09 Jul 2012 09:52:53 GMT

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;

Re: Distinct hours:Mins

vincent_ardiet — Mon, 09 Jul 2012 09:54:16 GMT

Hi,

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

Regards,

Vincent

Re: Distinct hours:Mins

jagannalla — Mon, 09 Jul 2012 10:32:23 GMT

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.

Re: Distinct hours:Mins

jvitantonio — Mon, 09 Jul 2012 10:52:10 GMT

That's formatted in your script:

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

Re: Distinct hours:Mins

vincent_ardiet — Mon, 09 Jul 2012 11:34:21 GMT

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

Re: Distinct hours:Mins

nagaiank — Mon, 09 Jul 2012 11:50:41 GMT

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

];

Re: Distinct hours:Mins

jagannalla — Mon, 09 Jul 2012 13:35:16 GMT

Thanks to all.

Re: Distinct hours:Mins

hic — Tue, 10 Jul 2012 17:17:27 GMT

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

Re: Distinct hours:Mins

jagannalla — Wed, 11 Jul 2012 09:05:07 GMT

Henric Cronstrom,

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

Re: Distinct hours:Mins

hic — Wed, 11 Jul 2012 09:25:02 GMT

Then you should truncate it to the nearest minute:

Time(Frac(Floor(Date,1/24/60)),'hh:mm')

HIC

Re: Distinct hours:Mins

jagannalla — Wed, 11 Jul 2012 09:48:05 GMT

His is correct.B'coz the timestamp values should align to right side of the column. Previous codes align to left side of the column.