Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jayati_shrivast
Contributor III
Contributor III

finding time difference with minimum function

Hi All,

I have 2 date fields as DATE 1  AND DATE2 in the format 'DD-MM-YYYY hh:mm:ss'. i need to find date difference between these 2 fields as      " min(Date1 ) - (DATE2)" and store this date diff as a separate field in table layout

i  should get data as follows  : I ONLY need to find the difference between first occurence of DATE1 with the corresponding DATE2

DATE1                                   DATE 2                                              DATEDIFF

17-10-2018 12:15:00        17-10-2018  12:17:00                00:02:00

17-10-2018 12:19:00         17-10-2018 12:21:00

18-10-2018 13:20:00         18-10-2018  13:25:00                00:05:00

18-10-2018 13:27:00          18-10-2018  13:31:00

..... AND SO ON...

PLS HELP ME OUT......

Thanks

 

 

 

 

 

 

 

 

 

 

3 Replies
rangam_s
Creator II
Creator II

date(date#(DATE1,'DD-MM-YYYY hh:mm:ss')-date#(DATE2,'DD-MM-YYYY  hh:mm:ss'),'hh:mm:ss')

 

Choose Duration in number formatting.

OmarBenSalem

Try sthing as follow:

 

Let's assume this is ur main table:

:

 

Table:

Load Date1,Date2, other fields ...;

 

Create a new date field based on Date1

 

Table:

Load Date1,Date2, other fields, Date(Date1) as MyDate ...;

 

Create a new aggregated table (group by MyDate):

 

load minDate2-minDate1 as Difference, MyDate

load min(Date1) as MinDate1, min(Date2) as minDate2, MyDate resident Table group by MyDate;

 

rangam_s
Creator II
Creator II

date(min(date#(DATE1,'DD-MM-YYYY hh:mm:ss'))-min(date#(DATE2,'DD-MM-YYYY  hh:mm:ss')),'hh:mm:ss')

 

Choose Duration in number formatting.