Hi Everyone,
I would like to do a filter:
True: this id has more than 5 orders in any three consecutive days.
I can't think of how to check three consecutive days? Can anyone help me? Below is some example.
Order Time | Order ID |
2018-01-13 17:14:26 | 1 |
2018-01-14 18:52:47 | 1 |
2018-01-15 19:47:10 | 1 |
2018-01-15 22:18:01 | 1 |
2018-01-16 12:42:01 | 1 |
2018-01-17 15:48:38 | 1 |
2018-01-17 19:09:40 | 1 |
2018-01-13 17:14:26 | 2 |
2018-01-14 18:52:47 | 2 |
2018-01-15 19:47:10 | 2 |
2018-01-15 22:18:01 | 2 |
2018-01-15 12:42:01 | 2 |
2018-01-17 15:48:38 | 2 |
2018-01-17 19:09:40 | 2 |
Please notice that althogh Order ID 2 has 5 orders in 0115 and 0117, It will be false because no order in 0106. That is not three consecutive days. Acutally I already filtered the 0 order date. Just a reminder.
So what I want for the output would be:
Order Time | Order ID |
2018-01-15 19:47:10 | 1 |
2018-01-15 22:18:01 | 1 |
2018-01-16 12:42:01 | 1 |
2018-01-17 15:48:38 | 1 |
2018-01-17 19:09:40 | 1 |
2018-01-13 17:14:26 | 2 |
2018-01-14 18:52:47 | 2 |
2018-01-15 19:47:10 | 2 |
2018-01-15 22:18:01 | 2 |
2018-01-15 12:42:01 | 2 |
For ID 1, we should take the newest 3 consecutive days, since it is consecutive from 0113 to 0117.
@Qiu Sorry for the misunderstanding. total amount of orders of 0113, 0114, 0115 is not equal or more than 5. So it will not be considered as true.
I agree with @Qiu that there seems to be a bit of discrepancy between your described logic and provided output.
To produce your output I interpreted your logic as: only take the LAST 3 sequential days of records. Following this I am able to produce your desired output and have attached the workflow.
Check it out and let me know if you have any questions on how this works.