Start a new topic

Cost file with start and end dates detecting overlaps - expanding range to create an array

I have a cost sheet that is manually entered with start dates and end dates and dimensions to match, the problem/challenge is how to flag if the start dates and end dates overlap?

Classic Omniscope (2.9) solution uses merge block with >= and <= and don't accept many to many, but what do you do in Evo (newer omniscope) version?


Omniscope workflow can help in a situation where there is a need to expand a value or date range. If the data contains one record per range (min-max), and we need to transform it into a dataset where every element of the array represents an individual record:

Add an Append block to bring together the dataset with the range and a sequence that contains all the values, lined in individual rows.  At this point there is no need to use a Join block, as many records that fall between the min/max values will not have a match. 

Add a Field Organiser block and create a formula field with syntax connecting the range with individual values:


SUBSET_UNIQUESLIST([Sequence],
SUBSET2(
[Sequence],[Sequence],
[Start date],[Finish date],
">=","<="))


This step will result in a list of dates against each campaign, all placed in a single cell. 

De-tokenise block will take care of the data orientation, transforming a series of single cells (containing lists of dates) into a new [Date] field with one record for every day of every campaign.

Aggregation on the [Date] field will finalise the operation, creating the summary with a record count against each date, showing if there was one or multiple campaigns running on a particular day.


Here is a worked demo.


Hi James Another solution is to use a zero-criteria many to many Join and to use formulas downstream. See example here: http://nightly.omniscope.me/Steve/Check+overlapping+ranges.iox/
Login or Signup to post a comment