topic Re: Date part missing. in QlikView

Date part missing.

qliktech_uk — Tue, 24 Oct 2017 20:37:20 GMT

I have a field taken from a spreadsheet that has a timestamp however for few records it just has the time. How do I populate the missing date? WHen I calculate the interval I get the wrong figures as its subtracting time (epoch date I assume) from the timestamp.

eg: 11:30:00 - 23/10/2017 11:10:10

Your help is appreciated.

Re: Date part missing.

Anil_Babu_Samineni — Wed, 25 Oct 2017 02:57:09 GMT

What are you expecting from above timestamp?

Re: Date part missing.

qliktech_uk — Wed, 25 Oct 2017 07:20:05 GMT

I just need to get the time difference. I tried stripping off the date part but wasnt successfull.

eg: 11:30:00 - 23/10/2017 11:10:10

So the above example should give me 20.

Re: Date part missing.

qliktech_uk — Thu, 26 Oct 2017 10:00:11 GMT

Can I use the function right and get the time part?

Re: Date part missing.

Anil_Babu_Samineni — Thu, 26 Oct 2017 10:14:34 GMT

May be this?

=Interval(Frac(Date(Date#('FieldName1,'hh:mm:ss'),'mm') - Date(Date#(FieldName2, 'DD/MM/YYYY hh:mm:ss'),'mm')),'mm')

Re: Date part missing.

tresB — Thu, 26 Oct 2017 10:19:14 GMT

If the fields are already timestamps, you could simply try like:

Interval(Field1-Frac(Field2), 'm')

If the fields are string, convert them using parsing functions like date#(), timestamp#().. and then apply the same logic

Re: Date part missing.

its_anandrjs — Thu, 26 Oct 2017 10:52:01 GMT

Then why not remove dates from the rows also and do interval match for the hh:mm:ss

Interval( Field1 - TimeStamp(TimeStamp#(Right(Field2,8),'hh:mm:ss'),'hh:mm:ss') , 'mm')