Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vinod22kv
Creator
Creator

Help in date and timestamp function.

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
1 Solution

Accepted Solutions
sunny_talwar

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"

];

View solution in original post

3 Replies
vishalarote
Partner - Creator II
Partner - Creator II

Hi Vinod,

why dont you take

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

it will take hour directly.

sunny_talwar

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"

];

vishalarote
Partner - Creator II
Partner - Creator II

Sorry use this,

mid( Date Tame,18,2) as Hour