Hi All,
I want to retrieve records which are >=24 hours for given location and customer id. How do I achieve this without having many Multi-row tools?
Original Data set
LOCATION_ID | CUSTOMER_ID | DATE | SEQUENCE_NO |
200100 | 2041470790 | 2018-08-24 02:10:00 | 1 |
200100 | 2041470790 | 2018-08-25 21:01:00 | 2 |
200100 | 2041470790 | 2018-08-30 11:20:00 | 3 |
200100 | 2041470790 | 2018-09-01 02:16:00 | 4 |
200100 | 2041470790 | 2018-09-02 16:32:00 | 5 |
200100 | 2041470790 | 2018-09-05 23:45:00 | 6 |
200100 | 2041470790 | 2018-09-06 11:51:00 | 7 |
200100 | 2041470790 | 2018-09-07 02:06:00 | 8 |
200100 | 2041470790 | 2018-09-07 16:15:00 | 9 |
200100 | 2041470790 | 2018-09-08 04:33:00 | 10 |
200100 | 2041470790 | 2018-09-08 16:56:00 | 11 |
200100 | 2041470790 | 2018-09-09 07:24:00 | 12 |
200100 | 2041470790 | 2018-09-10 09:00:00 | 13 |
200100 | 2041470790 | 2018-09-13 10:02:00 | 14 |
200100 | 2041470790 | 2018-09-14 02:24:00 | 15 |
200100 | 2041586960 | 2018-08-16 22:15:00 | 1 |
200100 | 2041586960 | 2018-08-17 11:48:00 | 2 |
200100 | 2041586960 | 2018-08-18 02:56:00 | 3 |
200100 | 2041586960 | 2018-08-23 11:40:00 | 4 |
Need to get
LOCATION_ID | CUSTOMER_ID | DATE | SEQUENCE_NO |
200100 | 2041470790 | 2018-08-24 02:10:00 | 1 |
200100 | 2041470790 | 2018-08-25 21:01:00 | 2 |
200100 | 2041470790 | 2018-08-30 11:20:00 | 3 |
200100 | 2041470790 | 2018-09-01 02:16:00 | 4 |
200100 | 2041470790 | 2018-09-02 16:32:00 | 5 |
200100 | 2041470790 | 2018-09-05 23:45:00 | 6 |
200100 | 2041470790 | 2018-09-07 02:06:00 | 8 |
200100 | 2041470790 | 2018-09-08 04:33:00 | 10 |
200100 | 2041470790 | 2018-09-09 07:24:00 | 12 |
200100 | 2041470790 | 2018-09-10 09:00:00 | 13 |
200100 | 2041470790 | 2018-09-13 10:02:00 | 14 |
200100 | 2041586960 | 2018-08-16 22:15:00 | 1 |
200100 | 2041586960 | 2018-08-18 02:56:00 | 3 |
200100 | 2041586960 | 2018-08-23 11:40:00 | 4 |
Thanks,
Dhanushka
Solved! Go to Solution.
Hi @Dhanushka_A
you can do it in a single Multi-Row tool if you use the following formula, grouped by Location_id and customer_id
if isnull([row-1:timediff]) then
0
elseif [Row-1:TimeDiff]+datetimediff([DATE],[Row-1:DATE],"seconds")>=24*60*60 then
0
else
[Row-1:TimeDiff]+ datetimediff([DATE],[Row-1:DATE],"seconds")
endif
The first if handles the first record in every group. The next one checks if the previous time difference + the current time difference >= 24 hours and sets the time difference for the current row to 0. The else clause adds the previous difference to the current and stores that. If effectively creates a running total that resets to 0 if it goes above 24 hours. Then you discard all the records that have a non-zero difference
Dan
Thanks Dan. I'm embarrassed to admit that I did not realize that we can use the new field "TimeDiff" within the expression until now.
Cheers,
Dhanushka