Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to compare the two line value and choose the min time.
e.g. I have a table below. When there are the same Case_ID, select the Min Arrival Time.
CASE_ID | SWO_ID | Arrival_Time | Departure_Time |
CAS-83372-F0N5 | SWO-70307 | 2015/12/12 0:00 | 2015/12/13 0:00 |
CAS-83376-F4Q3 | SWO-70308 | 2015/12/9 0:00 | 2015/12/10 0:00 |
CAS-83377-L5S3 | SWO-70310 | 2015/12/10 0:00 | 2015/12/11 0:00 |
CAS-83377-L5S3 | SWO-71345 | 2015/12/16 0:00 | 2015/12/17 0:00 |
CAS-83379-D1V5 | SWO-70314 | 2015/12/11 7:00 | 2015/12/12 7:00 |
Result: When the Arrival Time is Min value, to calculate the Duration.
CASE_ID | SWO_ID | Arrival_Time | Departure_Time | Duration |
CAS-83372-F0N5 | SWO-70307 | 2015/12/12 0:00 | 2015/12/13 0:00 | 1 |
CAS-83376-F4Q3 | SWO-70308 | 2015/12/9 0:00 | 2015/12/10 0:00 | 1 |
CAS-83377-L5S3 | SWO-70310 | 2015/12/10 0:00 | 2015/12/11 0:00 | 1 |
CAS-83377-L5S3 | SWO-71345 | 2015/12/16 0:00 | 2015/12/17 0:00 | 0 |
CAS-83379-D1V5 | SWO-70314 | 2015/12/11 7:00 | 2015/12/12 7:00 | 1 |
May be this:
Table:
LOAD CASE_ID,
SWO_ID,
Arrival_Time,
Departure_Time
FROM
[https://community.qlik.com/thread/213993]
(html, codepage is 1252, embedded labels, table is @1);
Left Join (Table)
LOAD CASE_ID,
Min(Arrival_Time) as Arrival_Time,
FirstSortedValue(Departure_Time, Arrival_Time) - Min(Arrival_Time) as Duration
Resident Table
Group By CASE_ID;
May be this:
Table:
LOAD CASE_ID,
SWO_ID,
Arrival_Time,
Departure_Time
FROM
[https://community.qlik.com/thread/213993]
(html, codepage is 1252, embedded labels, table is @1);
Left Join (Table)
LOAD CASE_ID,
Min(Arrival_Time) as Arrival_Time,
FirstSortedValue(Departure_Time, Arrival_Time) - Min(Arrival_Time) as Duration
Resident Table
Group By CASE_ID;
Hi Sunny,
I try your Function and it looks fine. Thanks a lot.