Solved: Re: Difference Between two date_time fields? - Qlik Community

Anonymous · ‎2014-09-08

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 · ‎2014-09-08

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

View solution in original post

sushil353 · ‎2014-09-08

Hi,

Try using

Timestamp#

Insteam of Timestamp

HTH

sushil

PrashantSangle · ‎2014-09-08

Hi,

Use inteval(time2-time,'Your format')

For details look into help menu.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Anonymous · ‎2014-09-08

Hi Thanx for ur reply..

But when i used timestamp# instead of timestamp its not working.....

when i try to get

year([START DATE]0 as my_year it is also does not working.

Anonymous · ‎2014-09-08

Hi..

Thanx for ur reply..

intervel function also does not working....

PrashantSangle · ‎2014-09-08

Hi,

Use like

Timestamp(Timestamp#([END DATE],'MM-DD-YYYY HH:mm:SS TT'))-Timestamp(Timestamp#([START DATE],'MM-DD-YYYY HH:mm:SS TT')) as Duration

OR

use above field in

Interval(Timestamp(Timestamp#([END DATE],'MM-DD-YYYY HH:mm:SS TT'))-Timestamp(Timestamp#([START DATE],'MM-DD-YYYY HH:mm:SS TT')),Format Code)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Report Inappropriate Content · ‎2014-09-08

Hi chandini,

Load from Call_Details...

timestamp(timestamp#(TextBetween(Call_Details,'<STARTDATETIME>','</STARTDATETIME>'),'MM-DD-YYYY HH:mm:SS TT'),'MM-DD-YYYY HH:mm:SS TT') as "START DATE",

timestamp(timestamp#(TextBetween(Call_Details,'<ENDDATETIME>','</ENDDATETIME>'),'MM-DD-YYYY HH:mm:SS TT'),'MM-DD-YYYY HH:mm:SS TT') as "END DATE"

Calc_Diff:

Interval([END DATE]-[START DATE]),Format Code) as 'DURATION

Resident Call_Details;

MarcoWedel · ‎2014-09-08

Please provide sample data

Anonymous · ‎2014-09-08

hi..

Its not working...............

Anonymous · ‎2014-09-08

Hi ..

Actually i got the data from database..in that one field contains xml realated data in that i saparate fields.(like startdata and enddata)...please find the attachment...