Difference Between two date_time fields?

[Anonymous]

Hi I ahve 2 date time fields I want get difference beetween two times ..

How can i can get difference ...

my code is:

Directory;

Master:
LOAD callid,calldata as Call_Details
FROM

[qvd];
directory;
Call_Details:
LOAD TextBetween(Call_Details,'<CALLID>','</CALLID>') as "CALL ID",TextBetween(Call_Details,'<STARTDATETIME>','</STARTDATETIME>') as "START DATE",TextBetween(Call_Details,'<ENDDATETIME>','</ENDDATETIME>') as "END DATE" resident Master;
DROP Table Master;


Calc_Diff:
LOAD [CALL ID],[START DATE],[END DATE],(Timestamp([END DATE],'MM-DD-YYYY HH:mm:SS TT')-Timestamp([START DATE],'MM-DD-YYYY HH:mm:SS TT')) as 'DURATION' Resident Call_Details;
DROP Table Call_Details;

i followed this code but when i try to display duration in listbox it does not show data...

please help me anyone.

[MarcoWedel · Accepted solution]

Hi,

one possible solution:

Call_Details:
LOAD *,
    Interval([END DATE]-[START DATE], 'hh:mm:ss') as DURATION;
LOAD TextBetween(@1,'<CALLID>','</CALLID>') as [CALL ID],
    Timestamp(Timestamp#(TextBetween(@1,'<STARTDATETIME>','</STARTDATETIME>'), 'YYYY-MM-DD hh:mm:ss.fff'),'DD/MM/YYYY hh:mm:ss') as [START DATE],
    Timestamp(Timestamp#(TextBetween(@1,'<ENDDATETIME>','</ENDDATETIME>'), 'YYYY-MM-DD hh:mm:ss.fff'),'DD/MM/YYYY hh:mm:ss')  as [END DATE]
FROM [http://community.qlik.com/servlet/JiveServlet/download/603856-123860/example.csv.xls] (txt, utf8, no labels, delimiter is '\t', msq);

hope this helps

regards

Marco

[Anonymous]

Hi i dont have lisenced version of Qlikview  can you please give the edit script code........

[MarcoWedel]

paste this into your personal edition's script editor:

Call_Details:

LOAD *,

     Interval([END DATE]-[START DATE], 'hh:mm:ss') as DURATION;

LOAD TextBetween(@1,'<CALLID>','</CALLID>') as [CALL ID],

     Timestamp(Timestamp#(TextBetween(@1,'<STARTDATETIME>','</STARTDATETIME>'), 'YYYY-MM-DD hh:mm:ss.fff'),'DD/MM/YYYY hh:mm:ss') as [START DATE],

     Timestamp(Timestamp#(TextBetween(@1,'<ENDDATETIME>','</ENDDATETIME>'), 'YYYY-MM-DD hh:mm:ss.fff'),'DD/MM/YYYY hh:mm:ss')  as [END DATE]

FROM [http://community.qlik.com/servlet/JiveServlet/download/603856-123860/example.csv.xls] (txt, utf8, no labels, delimiter is '\t', msq);

and reload.

regards

Marco

[PrashantSangle]

Hi,

Try with this,

Timestamp(Timestamp#([END DATE],'YYYY-DD-MM hh:mm:ss.fff'))-Timestamp(Timestamp#([START DATE],'YYYY-DD-MM hh:mm:ss.fff')) as Duration

OR

use above field in

Interval(Timestamp(Timestamp#([END DATE],'YYYY-DD-MM hh:mm:ss.fff'))-Timestamp(Timestamp#([START DATE],'YYYY-DD-MM hh:mm:ss.fff')),Format Code)

Date and Time Format must be YYYY-DD-MM hh:mm:ss.fff

Regards

[Anonymous]

hi ...

  when i try to load start date in listbox it does nt show any data.

Timestamp(Timestamp#(TextBetween(@1,'<STARTDATETIME>','</STARTDATETIME>'), 'YYYY-MM-DD hh:mm:ss.fff'),'DD/MM/YYYY hh:mm:ss') as [START DATE],

[Anonymous]

Hi...

Thank you for your reply.....

Its helpfull for me..............

[#8]

Thank you Marco...

[MarcoWedel]

You're welcome

Regards

Marco