Case Clauses for Mapping Rules

[anitadsalves]

Background

Currently, we have no functionality to do case clauses in the Menas UI:

The types of rules that require this functionality have been done through the API rather than the UI but we have gotten more requests for this to be a functionality in the UI.

Problem

Some use cases need to do apply a case clause before being able to do a mapping rule.

Example:

There is a dataset that holds client keys that are either an Source1 or Source2 in the same column. When trying to conform this against a mapping table the joins would be as follows:

Mapping Table : Dataset SourceSystem : SourceSystem (Needs to be a literal - either Source1 or Source2) SystemCode : ClientKey

Currently, it is not possible to have both Source2 and Source1 as the literal so we would only be able to conform against Source1 or Source2 but not both.

Proposed Solution

1. Dynamic Literal - CASE WHEN LEN(ClientKey) = 6 THEN “source1” ELSE CASE WHEN LEN(ClientKey) = 10 THEN “source2” ELSE “UNKNOWN” END END
2. Extra Clause in Join Condition - Mapping Table : Dataset SourceSystem : in ("source1","source2") SystemCode : ClientKey
3. Filter Mapping Table Before Executing Join - Mapping Table Filter Condition SELECT($"MappingTable.SourceSystem".isin("source1","source2"))

Example

[benedeki]

Solution to use two Mapping rules and Coalesce has been suggested: If you do 2 mapping table conformance rules and then apply coalesce on top of that it should provide good results. Coalesce is a new rule that takes a list of columns and applies first non null value.

This is being verified if it gives the results desired.