topic Re: Find Time Difference from two tables in QlikView

Find Time Difference from two tables

Wed, 01 Jul 2015 04:50:59 GMT

Table1:

Key	Received_DateTime
20150309-00029814_1	10/3/2015 2:28
20150309-00029814_2	1/4/2015 16:19
20150312-00004425_1	12/3/2015 3:47
20150312-00004425_2	1/4/2015 14:20
20150316-00001394_1	17/03/2015 02:21
20150316-00001394_2	8/4/2015 2:51

Table2:

Key	Released_DateTime
20150309-00029814_1	1/4/2015 15:07
20150309-00029814_2	1/4/2015 17:28
20150312-00004425_1	1/4/2015 10:52
20150312-00004425_2	1/4/2015 14:50
20150316-00001394_1	7/4/2015 16:33
20150316-00001394_2	8/4/2015 7:21

Need to find the Interval between Received_DateTime and Released_DateTime based on KEY

Regards

Re: Find Time Difference from two tables

PrashantSangle — Wed, 01 Jul 2015 05:05:25 GMT

Hi,

In script use join to combine both table

then use Resident load and interval() to calculate time difference

try like

Tabel1:

Load *,Key from Table 1;

join

Load *,Key from Table2;

Final:

Load Key,interval(Received_dateTime-Release_datetime,'hh:mm') as TimeDifference

Resident Table1;

Regards

Re: Find Time Difference from two tables

tresB — Wed, 01 Jul 2015 05:06:26 GMT

Load the tables as is. Then in the front end try like:

=Interval(Released_DateTime - Received_DateTime, 'dd:hh:mm:ss')

You can modify the interval output format as per your need, like 'hh' for difference hours, 'mm' for minutes.

Re: Find Time Difference from two tables

SatyaPaleti — Wed, 01 Jul 2015 06:35:32 GMT

Hi Kumar,

Try this

Table1:

load* Inline [

Key,Received_DateTime

20150309-00029814_1 ,10/3/2015 2:28

20150309-00029814_2 ,1/4/2015 16:19

20150312-00004425_1 ,12/3/2015 3:47

20150312-00004425_2 ,1/4/2015 14:20

20150316-00001394_1 ,17/03/2015 02:21

20150316-00001394_2 ,8/4/2015 2:51

];

join(Table1)

load * inline

[

Key,Released_DateTime

20150309-00029814_1 ,1/4/2015 15:07

20150309-00029814_2 ,1/4/2015 17:28

20150312-00004425_1 ,1/4/2015 10:52

20150312-00004425_2 ,1/4/2015 14:50

20150316-00001394_1 ,7/4/2015 16:33

20150316-00001394_2 ,8/4/2015 7:21

];

load Key,Interval((SubField(Received_DateTime,' ',1) &''&Time(SubField(Released_DateTime,' ',2))) - (SubField(Released_DateTime,' ',1) &''&Time(SubField(Received_DateTime,' ',2))),'dd:hh:mm') as Timedifference

Resident Table1;

Thank you,

Satya Paleti

Re: Find Time Difference from two tables

Wed, 01 Jul 2015 06:38:22 GMT

Join the two tables