How to determine if a date falls within two rows

[Ruby2]

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?

[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!

[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 : )

[vizAlter]

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

[grazitti_sapna]

Hi @Ruby2 ,try this.

Thanks.

[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  🙂

[AbhilashR · Accepted solution]

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? 

[Ruby2]

Hi AbhilashR

Thank you for your reply but Your flag does not match mine?  And it is flagging dates that are not a problem?  e.g. why does row 47 = 0?  The date ranges for Name41 do not overlap?  In my flag, Name35 is flagged because the dates 1/5/2020 to 30/06/2020 overlap in the two entries.  Thanks

[AbhilashR]

Hi @Ruby2, from what I can tell my latest workflow output matched your sample desired output. Please point me to the row number where you see a difference. 

Referring to Name 41 overlap: can you help me understand the date scenario where there would be an overlap? Purely going by From and To dates, it looks sequential but maybe I am missing something.

Let us know and we can tweak our logic to help you out.

[Ruby2]

Really sorry AbhilashR.    I don't know what happened there.  When I first ran your code it came back with mostly 1s and a couple of random 0s.  I couldn't figure it out.  I just downloaded again and reran and it's all fine now.  Your flags do match mine.  Perhaps I accidentally did something before I ran it the first time.  I've tried this on my live data and it works.  Thank you so much.