topic Re: Hours between two dates- LoadInLine (newbee), does not work in QlikView

Hours between two dates- LoadInLine (newbee), does not work

Anonymous — Wed, 09 May 2018 07:16:09 GMT

Hello Qlikview experts,

I'm trying newly to calculate simple difference between two dates in hours. I have tried to implement whats already discussed here but something does not work. What am I doing wrong? I'm new time strings. Thanks for help. I'm attaching data also -maybe there is wrong format?

My script is here:

[tab1]:
LOAD
Interval( Modified - Created, 'hh:mm' ) AS [Hours]
;

[tab2]:
LOAD

*
INLINE
[
Created, Modified
'3/20/2018 10:25', '3/20/2018 10:59'
]
;

LOAD ID,
Created,
Modified,
[Modified By]
FROM

(ooxml, embedded labels);

Re: Hours between two dates- LoadInLine (newbee), does not work

bhaskar_sm — Wed, 09 May 2018 07:32:11 GMT

Hi,

please find attached the Qvw.

and script

LOAD *,interval(num(Modified) - num(Created),'dd:hh:mm:ss')as Diff ;

LOAD ID,

Created,

Modified

FROM

[data.xlsx]

(ooxml, embedded labels, table is query);

Re: Hours between two dates- LoadInLine (newbee), does not work

jaumecf23 — Wed, 09 May 2018 07:33:52 GMT

Hi,

Qlikview is not able to identify that the fields Created and Modified are Dates. If you inform this to Qlikview then it works as you can see in the follow script:

[tab1]:

LOAD

Interval(Date#(Modified,'MM/DD/YYYY HH:mm') - Date#(Created,'MM/DD/YYYY HH:mm'), 'hh:mm' ) AS [Hours]

;

[tab2]:

LOAD

INLINE

[

Created, Modified

'3/20/2018 10:25', '3/20/2018 10:59'

]

;

Re: Hours between two dates- LoadInLine (newbee), does not work

Anonymous — Wed, 09 May 2018 11:04:09 GMT

thank you Bhasker, your solution works perfect!

Re: Hours between two dates- LoadInLine (newbee), does not work

bhaskar_sm — Wed, 09 May 2018 11:06:49 GMT

if your problem is resolved, can you mark it correct to close this ?

Re: Hours between two dates- LoadInLine (newbee), does not work

Anonymous — Wed, 16 May 2018 17:17:46 GMT

Hello Bhasker and all, after implementing the solution I received another challenge. How can I integrate condition of counting only "working hours" if that means MONDAY-FRIDAY 9AM-6PM?

Can you help me?

Thank you in advance