Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I Have 2 Tables
Received:
TRN_Received | Received_DateTime |
20150309-00029814 | 01/04/2015 16:19:02 |
20150309-00029814 | 10/03/2015 02:28:16 |
20150312-00004425 | 01/04/2015 14:20:03 |
20150312-00004425 | 12/03/2015 03:47:29 |
20150316-00001394 | 08/04/2015 02:51:44 |
20150316-00001394 | 17/03/2015 02:21:09 |
Released:
TRN_Released | Released_DateTime |
20150309-00029814 | 01/04/2015 15:07:25 |
20150309-00029814 | 01/04/2015 15:28:30 |
20150312-00004425 | 01/04/2015 10:52:52 |
20150312-00004425 | 01/04/2015 13:50:40 |
20150316-00001394 | 07/04/2015 16:33:07 |
20150316-00001394 | 08/04/2015 7:21:39 |
How can i find the difference between Released_DateTime and Received_DateTime
Thanks
try tis way,
=Interval(Interval(Sum(ProProductiveTime),'hh:mm:ss') -Interval(Sum(ProNonProductiveTime),'hh:mm:ss'),'hh:mm:ss')
use ur fields on the above expression
There are multiple TRN_Released in both the tables. Which ones do you need to calculate the difference?
Hi Senthil,
In order to find the interval , the compare based on the unique key other wise this yield in wrong result
Hi
Key | Received_DateTime | Released_DateTime |
20150309-00029814_1 | 10/03/2015 02:28:16 | 01/04/2015 15:07:25 |
20150309-00029814_2 | 01/04/2015 16:19:02 | 01/04/2015 17:28:30 |
20150312-00004425_1 | 12/03/2015 03:47:29 | 01/04/2015 10:52:52 |
20150312-00004425_2 | 01/04/2015 14:20:03 | 01/04/2015 14:50:40 |
20150316-00001394_1 | 17/03/2015 02:21:09 | 07/04/2015 16:33:07 |
20150316-00001394_2 | 08/04/2015 02:51:44 | 08/04/2015 7:21:39 |
In the above table , Received_DateTime is from table1 and the other from table2
Key is the Concatenated Column .
Is this the output you are expecting?
If yes then you can use the following script:
Table1:
LOAD *,
Interval(Released_DateTime - Received_DateTime, 'D hh:mm:ss') as Difference;
LOAD Key,
Timestamp#(Received_DateTime, 'DD/MM/YYYY hh:mm:ss') as Received_DateTime,
Timestamp#(Released_DateTime, 'DD/MM/YYYY hh:mm:ss') as Released_DateTime;
LOAD * Inline [
Key, Received_DateTime, Released_DateTime
20150309-00029814_1, 10/03/2015 02:28:16, 01/04/2015 15:07:25
20150309-00029814_2, 01/04/2015 16:19:02, 01/04/2015 17:28:30
20150312-00004425_1, 12/03/2015 03:47:29, 01/04/2015 10:52:52
20150312-00004425_2, 01/04/2015 14:20:03, 01/04/2015 14:50:40
20150316-00001394_1, 17/03/2015 02:21:09, 07/04/2015 16:33:07
20150316-00001394_2, 08/04/2015 02:51:44, 08/04/2015 7:21:39
];
HTH
Best,
Sunny