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

Posted about 4 years ago by James Clare

Post a topic
Un Answered
James Clare

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?

0 Votes


Sorted by
Steve Spencer

Steve Spencer posted about 4 years ago Admin

Hi James Another solution is to use a zero-criteria many to many Join and to use formulas downstream. See example here:

0 Votes


Paola Tomei posted about 4 years ago Admin

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:

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

0 Votes

Login or Sign up to post a comment