How to determine if a date falls within two rows

Hi. I am new to alteryx and using version 2019.4. I am creating an exception report to ensure that dates do not overlap for unique values across columns A-F. In my example, there is one set of unique values (Name45) where the dates overlap for 30/6/2020. I have attached anonymised data with the flag I would like to see. Can anyone help me get there please?

Date falls in two rows.yxmd

Date Time

Preparation

Accepted answers

AbhilashR

Hi @Ruby2, I tweaked my original approach ever so slightly and am able to match your desired output. Can you take a look and let us know if this helps?

Date can only fall into one range for each Name.yxmd

All comments

AbhilashR

Hi @Ruby2, not sure if I understood the larger context to your question but the attached solution helps replicate your desired output. I used a multi-row formula tool to compute differences between From and To dates across consecutive rows. Please make sure anytime you use a multi-row formula tool, your larger dataset is appropriately sorted before data enters the tool.

I hope this helps!

New_Date falls in two rows.yxmd

atcodedog05

Hi @Ruby2 ,

I am guessing when you said date overlap you meant overlap between EffTo and EffFrom

Hope this helps let me know if this solves your usecase.

Cheers and Happy Analyzing : )

Date falls in two rows.yxmd

vizAlter

Hi @Ruby2 — Hope the attached solution will be helpful, if not let me know.

Date falls in two rows_v1.yxmd

grazitti_sapna

Hi @Ruby2 ,try this.

Thanks.

Date falls in two rows-GS.yxmd

Ruby2

Thank you to everyone who has replied. I really appreciate you taking the time to look at this. It's very hard for me to get across what I am trying to do when I have to anonymise the data and can't explain what I am trying to do with it (company security). It appears that most of the solutions are only solving for the sample data, i.e. matching dates in the row immediately above or below. This is moving data so I can't just solve for the sample attached.

What I need to do is ensure that any business date does not fall into more than one effective range per Field4. (originally I said across all fields but lets simplify this).

I have uploaded another example. My thanks to anyone who is willing to give it another go and my apologies if I was confusing 🙂

Date can only fall into one range for each Name.yxmd

AbhilashR

Hi @Ruby2, I tweaked my original approach ever so slightly and am able to match your desired output. Can you take a look and let us know if this helps?

Date can only fall into one range for each Name.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703