Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Split date and time from a time field.

Hi, I have found the need to split date and time from a timestamp field.

This is how the field looks like in the source:

03.03.png

I have managed to split out the date by the use of date() - function

date(floor(create_time),'DD.MM.YYYY') as Dato1

The tricky part looks to be to just get out the whole hours from the create_time field.

What I have tried so far without any success.

frac(round(create_time,(1/24))) as [Incoming Time],

time(frac(create_time),'hh')   as [Incomming Time], Time#(Right(create_time,8),'hh') as [Incoming Time],

Time(Frac(TimeStamp#(create_time,'DD.MM.YYYY hh:mm:ss')),'hh') as [Incoming Time],

date(floor(create_time),'hh') as [Incoming Time],

Does anyone have any idea on how to achieve a "timestamp" with just the whole hour?

In my case, i just want 24 unique values from 00 - 23. From all the 21 million rows in the create_time field.

By using frac(round(create_time,(1/24))) as [Incoming Time],

I got this result, but the end result should be without Date and mm:ss just the hh left.


close.png


- Stian K.

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Split date and time from a time field.

Hi

You were almost there, I think with

frac(round(create_time,(1/24)))

Now just rearrange and format that - I think like this:

Time(Round(Frac(create_time), 1/24), 'hh')

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
MVP
MVP

Re: Split date and time from a time field.

may be something like below .... not 100% sure

=Time(create_time,'hh:00:00')

sunilkumarqv
Valued Contributor II

Re: Split date and time from a time field.

You can use Dual function to split the date and time

=Dual( Date( Floor( Date ), 'YYYY-MM-DD')  & '  _time ' Time( Frac( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'hh:mm:ss') as Incoming Time

Hope it helps

MVP
MVP

Re: Split date and time from a time field.

Hi

You were almost there, I think with

frac(round(create_time,(1/24)))

Now just rearrange and format that - I think like this:

Time(Round(Frac(create_time), 1/24), 'hh')

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Split date and time from a time field.

Or even this:


Round(Frac(create_time) * 24), 1)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Split date and time from a time field.

Yes, this also worked. Just added the missing (

Round((Frac(create_time) * 24), 1) as [Incoming Time],

Not applicable

Re: Split date and time from a time field.

Thanks worked like a charm

Community Browser