Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare Two Rows and Flag Both Rows

bleu
8 - Asteroid

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

 

 

 

5 REPLIES 5
pedrodrfaria
13 - Pulsar

Hi @bleu 

 

Here is how you can solve this problem:

 

pedrodrfaria_0-1615311835208.png

 

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.

RolandSchubert
16 - Nebula
16 - Nebula

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.

 

2021-03-09_18-47-20.jpg

 

What do you think?

 

Best,

 

Roland

bleu
8 - Asteroid

Works perfectly! You made it so concise.

Thank you so much Pedro.

bleu
8 - Asteroid

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!

agumaste
5 - Atom

How I can use this logic to find patients who got second (or multiple) dispenses of the drug within 5 day window?

 

2022-07-04_13-08-12.png

Labels