Hi,
I've got a slightly complicated problem with hundreds of thousands of rows.The best way is to show it:
Time Milliseconds Pass/Check
2021-03-05 05:25:51 640 Pass
2021-03-05 05:25:53 641 Pass
2021-03-05 05:27:58 100 Check
2021-03-05 05:27:58 105 Check
2021-03-05 05:28:37 166 Pass
2021-03-05 05:28:37 473 Pass
2021-03-05 05:28:42 106 Pass
- I want the date, hours, minutes and seconds to be exactly the same for the two dates to compare
- I want to find times that are very close to each other, within 5 milliseconds or less.
So the two date/times in bold would be the two I would want to investigate since they're 5 milliseconds apart.
- I want both rows flagged somehow so I can filter them out and just look at the pairs I need to check
I have several multi row formulas going and I'm starting to go in circles now, I'm starting from scratch.
Any ideas?
Thank you
Solved! Go to Solution.
Hi @bleu
Here is how you can solve this problem:
Use the Multirow to flag the fields that match. You can actually do them together, but to make it quicker I separated in two tools.
Pedro.
Hi @bleu ,
a different approach could be to join the table with itself on date and check the difference on milliseconds on the joined records. You have to exclude all joins of record with itself, did that using a RecordId.
What do you think?
Best,
Roland
Works perfectly! You made it so concise.
Thank you so much Pedro.
RolandSchubert I thought about the Absolute value for getting the difference but then I got stuck after that. Never would have thought of using the Record ID and a self Join.
Thank you!
How I can use this logic to find patients who got second (or multiple) dispenses of the drug within 5 day window?