topic Re: Help in date and time. in QlikView

Help in date and time.

vinod22kv — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have two fieldes given bellow. B is e mail received on and A Is email responded i want to calculate the average time how much it is taking to reply for mail. for this i need A-B/count.

Now i need help how to do A-B,

A	B
5/21/2018 9:30	Sun, 20 May 2018 23:35:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:37:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:38:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:39:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:40:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:41:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:42:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:43:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:44:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:45:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:46:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:47:20 +0100
5/21/2018 9:30	Sun, 20 May 2018 23:51:20 +0100

Thanks and Regards,

Vinod.

Re: Help in date and time.

Frank_Hartmann — Mon, 21 May 2018 20:41:03 GMT

Load     *,
        RowNo() as Row, 
        Date(Date#(left(right(B,26),20),'DD MMM YYYY hh:mm:ss'),'M/DD/YYYY hh:mm:ss') as AOld,
        Date(ConvertToLocalTime(Date(Date#(left(right(B,26),20),'DD MMM YYYY hh:mm:ss'),'M/DD/YYYY hh:mm:ss'), 'GMT+01:00'),'M/DD/YYYY hh:mm:ss') as BNew,        
        Date(Date#(A,'M/DD/YYYY hh:mm'),'M/DD/YYYY hh:mm:ss') as ANew;
LOAD * INLINE [
    
    A, B
    5/21/2018 9:30, "Sun, 20 May 2018 23:35:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:37:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:38:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:39:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:40:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:41:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:42:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:43:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:44:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:45:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:46:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:47:20 +0100"
    5/21/2018 9:30, "Sun, 20 May 2018 23:51:20 +0100"
];

Re: Help in date and time.

vinod22kv — Tue, 22 May 2018 09:57:52 GMT

It is working fine with inline load.

But it is not working when we have loded the excel.

Re: Help in date and time.

Frank_Hartmann — Tue, 22 May 2018 10:24:41 GMT

whats your date format in Excel?

Can you Upload your Excel file? You can erase all the columns except the two date columns...

Re: Help in date and time.

vinod22kv — Tue, 22 May 2018 12:40:54 GMT

Hi,

It is working fine the format in the excel was different.

Thanks a lot for your help..

Re: Help in date and time.

vinod22kv — Thu, 24 May 2018 11:53:58 GMT

Hi,

Can you please extract hours form the same data and share me the script.

i am using bellow function

date(date#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS Hour,

Timestamp(Timestamp#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS hor,

but i am not able to get the hour for all the records.

Please help me out.

Re: Help in date and time.

Frank_Hartmann — Fri, 25 May 2018 07:41:25 GMT

Date(Date#(left(right([Date Time],26),20),'DD MMM YYYY hh:mm:ss'),'hh') as AHour,
Date(ConvertToLocalTime(Date(Date#(leftDate(ConvertToLocalTime(Date(Date#(left(right(B,26),20),'DD MMM YYYY hh:mm:ss'),'M/DD/YYYY hh:mm:ss'), 'GMT+01:00'),'hh') as BHour,