SAC planning model - delete duplicate records

[antonclv19]

Hi All

I am wanting to delete duplicate records from a model using a specified key (combination of dimensions).  For example, in the below "BEFORE" image, I have 2 records for Customer = '123'.  I would like to delete the top record where Customer Group = '#'.  I do not want to delete all records Customer Group = '#', because for the likes of Customer = '456', there is only 1 record.

I have tried coding an advanced formula data action, but can't seem to get it right.

Can someone please assist with the correct code needed, or perhaps suggest another way of giving the same result?

Thanks

Anton

[MichalCK]

you need to aggregate on customer group and divide not aggregated value by aggregated. If the result is less than 1 then you have duplicates and you delete Customer Group #

VARIABLEMEMBER #CGROUP OF [d/CustomerGroup]

DATA([d/CustomerGroup] = #CGROUP) = RESULTLOOKUP()

IF RESULTLOOKUP() / RESULTLOOKUP([d/CustomerGroup] = #CGROUP) < 1 THEN
	DELETE([d/CustomerGroup] = "#")
ENDIF

more or less like that. I've not tested the above code, it should be possible to remove unwanted records. But if it is worth the effort, depends.

[antonclv19]

@MichalCK - thanks for the suggestion, I'll see if that works.

[MaxT]

@MichalCK, I was aware of that. I tried your suggestion too but then I got this error message instead:

[MichalCK]

@MaxT, you have to specify a measure either through MEMBERSET [d/Measure] = "XXXX" or directly in RESULTLOOKUPs

[MaxT]

Hi @MichalCK,

Could you show me a code where this worked? I think I've tried all possibly options, including your suggestions but I don't seem to obtain the preferred outcome.

Thanks in advance! 

[MichalCK]

hi @MaxT, I don't have same example as you do. Code I gave you is a template which after some modifications should work

[MichalCK]

Your case does not seem to be complicated. The code I wrote, does aggregate CustomerGroup into variable, then we check if current value divided by aggregated value is lower than 1. This will indicate that there are multiple CustomerGroup values. And then we delete # Customer group. I'm not able to provide you exact solution, as each model is specific and code would need to be adjusted to meet your data model (like eg. measure selection if you have multiple measures).

[MaxT]

@MichalCK, Could you a snipped of the code where you aggregated a dimension into a variable? Because I could understand how that would make your initial code possible.

[MichalCK]

imagine aggregation as temporary table where value of aggregated column is replaced by #CGROUP, and amount is aggregated to 4000 (in your case). When you divide CustomerGroup AA by #CGROUP you will get value 0.5 which is < 1, and then we delete # Customer Group (this will execute probably few times, but will hit # just once). My code snippet will not help you. Please try to debug it to see what is there in data.