Hi, I couldn't find the exact solution to my problem, so creating a new topic. I have the following dataset: Customer IDProduct IDStart DateEnd DateValue111111202001312020123111112222020013120201231211133320200131202012313222111202001312020123142222222020013120201231522222220200401202004156222333202001312020123173331112020013120201231833311120210131202112319 I need to create a workflow which adds a column at the end and flags the records that overlap with other ones. So in the example above records with values 5 and 6 should be flagged. FYI...The real dataset contains around 100k records. Thanks, Pedram

Thanks - think it looks good! 🙂

Try the attached. I fixed up the last few issues. Time Overlaps_JD (1).yxmd

Missing tool and connection restored! Guessing when I was screenshotting deleted it! But has problems - will see what is wrong with it and get it fixed. Will take some work - but will get a working demo for you asap Time Overlaps_JD (1).yxmd

Find Overlapping Timestamps

peddy

Hi,

I couldn't find the exact solution to my problem, so creating a new topic.

I have the following dataset:

Customer ID	Product ID	Start Date	End Date	Value
111	111	20200131	20201231	1
111	222	20200131	20201231	2
111	333	20200131	20201231	3
222	111	20200131	20201231	4
222	222	20200131	20201231	5
222	222	20200401	20200415	6
222	333	20200131	20201231	7
333	111	20200131	20201231	8
333	111	20210131	20211231	9

I need to create a workflow which adds a column at the end and flags the records that overlap with other ones. So in the example above records with values 5 and 6 should be flagged.

FYI...The real dataset contains around 100k records.

Thanks,

Pedram

Date Time

Accepted answers

All comments

atcodedog05

Hi @peddy

Sorry jumped too fast.

atcodedog05

Hi @peddy

Have a doubt why are last 2 rows not colored. Same Customer and product ID but different time.

Can you be clear with the logic.

333	111	20200131	20201231	8
333	111	20210131	20211231	9

jdunkerley79

I would suggest:

First, sort the data by Customer, Product, Start Date

Use a multirow formula you can create a new column which is the current max end date of a period

Then a second multi-row can detect overlaps

Have attached a sample

Time Overlaps.yxmd

peddy

@atcodedog05

because one is 2020 and the other is 2021

peddy

@jdunkerley79

Thanks - However, your solutions doesn't fully work. As you can see below line 2, 10 and 15 should have been true. I'm assuming the formula in the second multi-formula tool needs to be changed, but not 100% sure to what exactly.

I did try a slightly different approach by summarizing on customer and product to get the max end date which seemed to resolve one of false negatives, but that's as far as I managed to get...

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167