I'm developing a dashboard which currently tracks the time spent on a building site. Some of the data automatically collected for this is the Check In Time and Check Out Time. From this, we calculate a value for Minutes on Site, using Check Out Time - Check In Time. However, this means that we cannot get a value for Minutes On Site while a person is still on site, as they do not have a Check Out Time.
How do I create a column on a table which displays Time (Now) - Check In Time in minutes?
The Check In Time is formatted as below;
"in_time_num",TO_CHAR(TO_DATE(OT."In Date", 'DD/MM/YYYY HH24:mi:SS'), 'HH24:mi') as IN_DATE_TIME,
Minutes on Site is calculated as below;
ROUND((to_DATE(OT."Out Date",'DD/MM/YYYY HH24:mi:SS') - to_DATE(OT."In Date",'DD/MM/YYYY HH24:mi:SS')) * 24 * 60) AS "Minutes On Site",
Depending on your specific DB the syntax may change, but it looks like Oracle, so you would use Sysdate. That said, this isn't a Qlik question at all... in Qlik, the syntax would be Now(), but Qlik doesn't have a TO_DATE function.
ROUND((to_DATE(OT."Out Date",'DD/MM/YYYY HH24:mi:SS') - SYSDATE) * 24 * 60) AS "Minutes On Site",
Depending on your specific DB the syntax may change, but it looks like Oracle, so you would use Sysdate. That said, this isn't a Qlik question at all... in Qlik, the syntax would be Now(), but Qlik doesn't have a TO_DATE function.
ROUND((to_DATE(OT."Out Date",'DD/MM/YYYY HH24:mi:SS') - SYSDATE) * 24 * 60) AS "Minutes On Site",
