topic Re: Convert hours to date in QlikView

Convert hours to date

skzwane01 — Sat, 16 Nov 2024 03:31:49 GMT

Hi,

I have an excel data file which has the below data and I would like to convert the data into date format.

TriggerDate

07:06.9

18:02.4

23:11.4

21:34.0

52:13.9

29:33.5

Please kindly assist.

Kind Regards,

Sanele Zwane

Re: Convert hours to date

sunny_talwar — Mon, 20 May 2019 15:20:11 GMT

Based on the input file provided, what is the number you are hoping to see? What Date should 30:03.8 correspond to?

Re: Convert hours to date

skzwane01 — Mon, 20 May 2019 16:29:22 GMT

Hi,

Thank you for response. Please see below data. I just noticed that there is another field in the data that can assist in deriving the actual date (Date 2). I say this because I see some the "Date 2" data is on the correct date format however I need to make the data consistent

Based on the data below - Please assist in converting the "Date 2" to the "Date 1" format and the calculation of the Date difference.

Hope this makes sense

Date 1	Date 2	Date Difference
01/09/2016 10:38	31/08/2016 18:42
08/07/2016 09:06	40:34.3
16/09/2016 13:24	40:34.2
28/07/2016 11:21	26/07/2016 19:06
29/07/2016 10:18	55:54.9
29/09/2016 13:19	28/09/2016 15:59

Re: Convert hours to date

sunny_talwar — Mon, 20 May 2019 16:37:53 GMT

But what date are you expecting this to be? 40:34.3

Re: Convert hours to date

skzwane01 — Mon, 20 May 2019 18:23:21 GMT

40:32.3 is 40 hours and 32 minutes and 3 seconds before the date :'08/07/2016 09:06'

so I need the result of the following calculation in a date format:(08/07/2016 09:06 - 40 hours and 32 minutes and 3 seconds)

Re: Convert hours to date

sunny_talwar — Tue, 21 May 2019 11:53:37 GMT

Try this

Table: LOAD [Date 1], TimeStamp(If(Num([Date 2]), [Date 2], [Date 1] - Interval#([Date 2], 'hh:mm.ss'))) as [Date 2], Interval(If(Num([Date 2]), [Date 1] - [Date 2], Interval#([Date 2], 'hh:mm.ss')), 'hh:mm.ss') as [Date Difference]; LOAD * INLINE [ Date 1, Date 2 01/09/2016 10:38, 31/08/2016 18:42, 08/07/2016 09:06, 40:34.03, 16/09/2016 13:24, 40:34.02, 28/07/2016 11:21, 26/07/2016 19:06, 29/07/2016 10:18, 55:54.09, 29/09/2016 13:19, 28/09/2016 15:59, ];