Finding duplicate combination/inputs in different dimension

Arnab116

I have module dimensioned by TRID(1-100), Business Type(Type 1, Type2, etc.) and Region(EMEA,APAC,NA,LATAM). This module is used to define rules for each business type and Region. For example:

So, these are the inputs/rules for each region . In this example For EMEA we have 3 rules Rule 1. Segment = A Partner = C
Rule 2. Product = B

Rule 3. Product = X Partner = Y Attribute 1 = Z.

Now the requirement is if user tries to input similar rules for another region (APAC/NA/LATAM) it should show me an error of "Duplicate Rule".

Initially I thought of concatenate the inputs and create a list and then apply a finditem
on the concatenation of the inputs to check its already present or not but for
that it seems we have to create a action button which needs to be run after every rule is entered which will be a long process for the user.

So any better solution is appreciated.

Thank you,

Arnab

rob_marshall

@Arnab116

As @KirillKuznetsov stated, you will need to create a flat list (with a top member - call it Rules) of all the business rules. Create logic (isfirstoccurrence) that does not build the Rules list if the isfirstoccurrence is false. You can also use this (if false) to identify duplicate values.

Then, create a binary list (go into excel and use the function Dec2Bin. I used 6 as the 2nd parameter because you have 6 dimensions (Segment→Attribute 3)

you should end up with 63 members

Dimensionalize this by the list on the rows 1-XX. If the row list is not blank and the binary has a 1 for that position, then return the item of that attribute. Then create a finditem(Rules, member) to see if it is a valid Rule. Also, change the summary to LastNonBlank which will create a weighting on it (the most granular will be the last one chosen). Any members in the Top Level member will be the most granular and valid member.

Lastly, I don't believe you need to dimensionalize this module by region because the rule is applied to all regions and can't be duplicated.

Good luck,

Rob

KirillKuznetsov

@Arnab116

Create a list of all possible combinations and then count how many times these combinations are present in your module.