topic Re: Re: Difference Between two date_time fields? in QlikView

Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 07:25:01 GMT

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.

Re: Difference Between two date_time fields?

sushil353 — Mon, 08 Sep 2014 07:27:13 GMT

Hi,

Try using

Timestamp#

Insteam of Timestamp

HTH

sushil

Re: Difference Between two date_time fields?

PrashantSangle — Mon, 08 Sep 2014 07:29:32 GMT

Hi,

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

For details look into help menu.

Regards

Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 07:34:46 GMT

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.

Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 07:38:06 GMT

Hi..

Thanx for ur reply..

intervel function also does not working....

Re: Difference Between two date_time fields?

PrashantSangle — Mon, 08 Sep 2014 07:41:24 GMT

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

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

Re: Difference Between two date_time fields?

Mon, 08 Sep 2014 07:57:26 GMT

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;

Re: Difference Between two date_time fields?

MarcoWedel — Mon, 08 Sep 2014 07:58:40 GMT

Please provide sample data

Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 08:47:36 GMT

hi..

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

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 08:51:01 GMT

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...

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 08:51:19 GMT

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...

Re: Difference Between two date_time fields?

Mon, 08 Sep 2014 08:55:05 GMT

Hi,

Make sure that you are passing correct timestamp# format while taking text date into date format while loading ...

Check StartDate EndDate format before proceeding for interval calculation...

Re: Difference Between two date_time fields?

Mon, 08 Sep 2014 09:01:36 GMT

You need to pass format like below to match,

(...,'YYYY-DD-MM hh:mm:ss:ms')

timestamp(timestamp#(TextBetween(Call_Details,'<STARTDATETIME>','</STARTDATETIME>'),'YYYY-DD-MM hh:mm:ss:ms'),'YYYY-DD-MM hh:mm:ss') as "START DATE",

like others...

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 09:09:40 GMT

hi...

Actually i got the data from database in that one of the field of dtabase is in xml formate.i saparate data from xml(like start data and end data).i want to display difference between two dates.

End date is22/06/2014 04:27:01

Start date is 22/06/2014 04:25:01

please give me solution......

Re: Difference Between two date_time fields?

Mon, 08 Sep 2014 09:22:16 GMT

Try this now,

Calc_Diff:
LOAD [CALL ID],[START DATE],[END DATE],

Interval(Timestamp(Timestamp#([END DATE],'YYYY-DD-MM hh:mm:ss:ms'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#([START DATE],'YYYY-DD-MM hh:mm:ss:ms'),'DD-MM-YYYY hh:mm:ss'),'DD:hh:mm:ss') as 'DURATION'

Resident Call_Details;

regards,

harshal

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 09:32:57 GMT

Hi Harshal..

Even also its not working....................

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 09:34:54 GMT

When i try to appply year(startdate) as my_year, its also doesnot work..........all date functions also does work...............

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 09:36:31 GMT

Hi...

All date functions also does not work...

Re: Difference Between two date_time fields?

Mon, 08 Sep 2014 09:47:11 GMT

year(startdate) It will not come because your startDate is in text format..

you need to convert it in date format from text..

Try this for year,

year(date(date#(startDate,'YYYY-DD-MM hh:mm:ss:ms'),'DD-MM-YYYY')) as year

check whether your year comes or not...

Re: Re: Difference Between two date_time fields?

Anonymous — Mon, 08 Sep 2014 10:04:13 GMT

no it does not display year....