topic Re: extract field in QlikView

extract field

master_student — Fri, 09 Sep 2016 09:58:14 GMT

Hello nice people,

I have a date field in my database that contains date and time

I would like to extract only time to display it in QlikView.

Any idea, how to do that?

Thanks

Re: extract field

arulsettu — Fri, 09 Sep 2016 10:03:05 GMT

try this

Timestamp(Timestamp#('08/01/2013 08:21:00 AM','DD/MM/YYYY hh:mm:ss tt'),'hh:mm:ss')

Re: extract field

ToniKautto — Fri, 09 Sep 2016 10:04:57 GMT

The format expression in QlikView should be either of the below options. The shorter one will work if your format variables in QlikView matches the incoming format from data base.

Notice, the data is loaded as a timestamp, the date portion is removed by using Frac(), and finally the time value is formatted.

Time(Frac(MIN_DATE_DEB))

Time(Frac(Timestamp#(MIN_DATE_DEB, 'DD/MM/YYYY hh:mm:ss')))

Re: extract field

ToniKautto — Fri, 09 Sep 2016 10:06:41 GMT

This function will create something that looks like a time, but it will logically be a timestamp. The underlying number in the timestamp must be reformatted to a decimal value with 0 integer. See my example below, where the numerical format is done with Frac().

The outer function should be Time(). It should not require format pattern, as it will then revert to the applcation default pattern.

Re: extract field

saimahasan — Fri, 09 Sep 2016 10:23:43 GMT

Please check whether your time field is proper date and time field...

Then you can extract time by using:

=Timestamp([Field Name],'hh:mm:ss')

As i did for now(), its working fine,

=Timestamp(Now(),'hh:mm:ss')

Re: extract field

Anil_Babu_Samineni — Fri, 09 Sep 2016 11:10:19 GMT

Let's assume your date field like

MIN_DATE_DEB

08/01/2013 08:21:00

Perhaps This

Timestamp(Timestamp#(MIN_DATE_DEB, 'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')

Re: extract field

ToniKautto — Fri, 09 Sep 2016 11:25:24 GMT

Please see my previous comment(s) above. This is not a accurate way to reformat a timestamp into a time.

Re: extract field

Anil_Babu_Samineni — Fri, 09 Sep 2016 11:31:22 GMT

Yes, I've seen that - This scenario how do we know is that is running in the morning or After noon

Because, You used simple TIME - So, It could be the time in the evening as well. This format is 12 not 24 Hours then how do we identify?

Really help needed TONI.

Re: extract field

sunny_talwar — Fri, 09 Sep 2016 11:42:22 GMT

hh:mm:ss will be 24 hour format and hh:mm:ss TT is 12 hour format with PM/AM at the end. So Time alone will tell you if its morning or evening

Re: extract field

miguelbraga — Fri, 09 Sep 2016 12:01:19 GMT

You can do simply this:

time(MIN_DATE_DEB) as TIME_MIN_DATE_DEB,

It will make exactly what you want without need much

Regards,

Re: extract field

ToniKautto — Fri, 09 Sep 2016 12:09:23 GMT

Anil, I am not sure you understand the formatting logic in QlikView.

A proper time is represented by a decimal value, where integer is a zero. A timestamp is a decimal value, where integer is not a zero. Converting a timestamp into a time value requires using Frac() to remove the non-zero integer value.

By using Time() or Time#() you will apply proper meta data tags to your time data, which means the data will be tagged as time. It also means that the functions by default will apply the TimeFormat variable value as its format pattern, which means you do not have to type the format pattern every time you convert data. This make you script easier to maintain, and mean you control the output format from your format variable and not in a each function call.

Re: extract field

ToniKautto — Fri, 09 Sep 2016 12:10:45 GMT

This will not generate a proper time value, for the third time in this thread.