Loading and manipulating datetimeoffset fields

WildCat · ‎2020-02-05

Hello, I have two questions, and I'm grateful for help. I'm trying to load data that has multiple date fields in the datetimeoffset format (e.g. 2020-02-05 11:13:34 -07:00). Directly loaded, they come in as a string. So I used CONVERT(VARCHAR(19),DTOField,20) statements in the load script to get rid of the offset part. For about half of the fields, Qlik recognized them as date fields and split them into all the nice date parts like Quarter, but the remaining fields still came in as strings. Can anyone suggest why that might happen? My second question is how do I subtract two datetime fields? e.g. I want (2020-02-05 10:11:52) - (2020-02-05 10:07:22) to return 00:04:30 (and actually ideally I'd really like to end up with 4.5 (minutes) ) because I want to chart an average response time by unit. At least I think that's what I want. Thanks so much for any help you can offer!

pedrobergo · ‎2020-02-10

Hi @WildCat ,

In your first question, you use the statement "CONVERT(VARCHAR(19),DTOField,20) " in the SQL syntax. Maybe you might use it inside the LOAD syntax. Let me tell you how it works.

Table:
LOAD field1, 
     Date#(field2, "YYYY-MM-DD hh.mm.ss.ff") as fieldDate, 
     fieldN;
SQL SELECT field1, 
           field2, 
           fieldN
FROM database;

Note the statement has two parts: LOAD and SQL SELECT. Each row gathered by SQL SELECT from database flows to LOAD statement continuously. You can rename the fields and use many function to convert and transform the data fields.

So if you want to convert the date fields, you can use the Date#() or Date() functions, like above.

In your second question, you want to convert the 60 base to 100 base (00:04:30 to 4,5), please verify this post

https://community.qlik.com/t5/New-to-QlikView/Convert-a-duration-into-a-number-of-hours/m-p/919267

Good luck,

Pedro

Qlik Sense