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
Steve Spencer posted
over 4 years ago
AdminBest Answer
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.
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
Steve Spencer posted over 4 years ago Admin Best Answer
0 Votes
2 Comments
Steve Spencer posted over 4 years ago Admin Answer
0 Votes
Paola Tomei posted over 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:
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