community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Multi-Row Formula: Testing Against All Rows in Group?

Atom

Looking for help using the multi-row formula (or a different tool) to test against all rows in a group. It's tricky because a) the groups all have varying amounts of records and b) I need to test against every record in the group, not just the preceding one.

 

Each record has a validity period (Period Start and Period End). I want to flag all records that have a validity period that overlaps with another record(s) in the group.

 

For example, in Group A, the second record is valid against the first because its Period Start occurs before the previous record's Period End. As a result, both are flagged.

 

In Group B, the last record does not overlap with any time periods of the other Group B records, so it does not get flagged. No records overlap in Groups C or D. In Group E, only the last 2 records overlap, so both are flagged.

 

Does anyone know if this might be possible? 

ACE Emeritus
ACE Emeritus

[ ... edit: removed... ]

Highlighted
Alteryx Certified Partner

Hello @em1 

 

I've put together the attached solution that achieves the desired result. The path I took was to generate a list of all days for each record and see which days had more than one record from the same group. One I determined which records had overlap with another, I joined that information back to the original table and cleaned it up.

 

Let me know if you have any questions.

Atom

Thanks, @CharlieS !

 

This was exactly what I asked for. With a few tweaks it's running nicely to solve a bigger problem on the real data. Not sure how it will scale if the data grows much more but it definitely gets the job done!

Labels