topic Re: Help in date and timestamp function. in QlikView

Help in date and timestamp function.

vinod22kv — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

have to extract the hours from one field and the field is attached hear. but output is not comming for few records, i have used bellow formulas,

date(date#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS Hour,

Timestamp(Timestamp#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS hor,

The out come what i am getting is attached bellow.

Date Tame

Sat, 19 May 2018 11:09:46 +0100

Sat, 19 May 2018 09:09:46 -0400 (EDT)

Sat, 19 May 2018 14:25:21 +0100

Sun, 20 May 2018 22:38:39 +0000

Mon, 21 May 2018 05:22:03 +0000

Mon, 21 May 2018 03:02:16 -0400 (EDT)

Mon, 21 May 2018 07:06:04 +0000

Mon, 21 May 2018 07:22:44 +0000

Mon, 21 May 2018 07:55:21 +0000

Mon, 21 May 2018 08:24:42 +0000

Sat, 19 May 2018 16:27:29 +0000

Sat, 19 May 2018 17:37:01 +0100

Mon, 21 May 2018 08:52:39 +0100

Mon, 21 May 2018 08:49:44 +0000

Mon, 21 May 2018 08:57:30 +0000

Re: Help in date and timestamp function.

vishalarote — Thu, 24 May 2018 11:40:54 GMT

Hi Vinod,

why dont you take

=mid( Date Tame,18,19) as Hour

it will take hour directly.

Re: Help in date and timestamp function.

sunny_talwar — Thu, 24 May 2018 11:42:27 GMT

Try this

Table:

LOAD *,

TimeStamp#(Left(SubField([Date Time], ', ', -1), 20), 'DD MMM YYYY hh:mm:ss') as TimeStamp,

Hour(TimeStamp#(Left(SubField([Date Time], ', ', -1), 20), 'DD MMM YYYY hh:mm:ss')) as Hour;

LOAD * INLINE [

Date Time

"Sat, 19 May 2018 11:09:46 +0100"

"Sat, 19 May 2018 09:09:46 -0400 (EDT)"

"Sat, 19 May 2018 14:25:21 +0100"

"Sun, 20 May 2018 22:38:39 +0000"

"Mon, 21 May 2018 05:22:03 +0000"

"Mon, 21 May 2018 03:02:16 -0400 (EDT)"

"Mon, 21 May 2018 07:06:04 +0000"

"Mon, 21 May 2018 07:22:44 +0000"

"Mon, 21 May 2018 07:55:21 +0000"

"Mon, 21 May 2018 08:24:42 +0000"

"Sat, 19 May 2018 16:27:29 +0000"

"Sat, 19 May 2018 17:37:01 +0100"

"Mon, 21 May 2018 08:52:39 +0100"

"Mon, 21 May 2018 08:49:44 +0000"

"Mon, 21 May 2018 08:57:30 +0000"

];

Re: Help in date and timestamp function.

vishalarote — Thu, 24 May 2018 11:46:37 GMT

Sorry use this,

mid( Date Tame,18,2) as Hour