How to verify uniqueness for a combination of fields

skoneru · April 5, 2021, 7:11pm

Here is the dataset we have and wanted to create a rule to verify " For the ID and Description combination, should have the same date" else 'Fail"
|ID|Description|Status*|Date|
|Z00009055|ABCTWT01|IN_PROGRESS|2023-01-11|
|Z00009055|ABCTWT01|DISCONTINUED|2023-01-11|
|Z00009055|ABCTWT01|IN_PROGRESS|2023-01-11|
|Z00009055|ABCTWT01|DISCONTINUED|2023-01-11|

Can someone please help with this?

dgudkov · April 5, 2021, 7:52pm

You can check it by counting the number of distinct dates for each combination (group) of ID and Description. This can be done with the “Aggregate” action with the “Count distinct” aggregation. If at least one count is greater than 1 then halt project execution.

See the example below:
distinct-in-group.morph (3.5 KB)

skoneru · April 5, 2021, 9:54pm

Thanks. The project doesnt open as its built on 4.7 and I dont see an update to 4.7. I am currently on 4.6

dgudkov · April 5, 2021, 10:01pm

Version 4.7 is already available on our website but the update notifier isn’t triggered yet as we’re ironing out minor things. The example is basically one “Aggregate” action with a few helper actions. See the screenshot below.