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

[JefB]

Something like this?

MEMBERSET [d/CUSTOMER] = "123"
MEMBERSET [d/CUSTOMERGROUP] = "#"
MEMBERSET [d/SALESORG] = "1000"
MEMBERSET [d/MEASURE] = "Amount"
DELETE()

[antonclv19]

Hi @JefB - thanks for the reply, but I am looking for a more dynamic solution as I have thousands of "duplicate" records in the table where one record has Customer Group = '#' and the other has it populated. The screenshot I gave was just for reference purposes.

[deshmukh]

just trying to understand, cant we handle this in source system if it’s difficult in SAC

[JefB]

Then you can use MichalCK's code example, should work

[MaxT]

Hi @JefB 

I'm struggling with a similar issue. Therefore I was very happy to read this thread and try your proposed code.

However, when that is tried, it gives the error message that the operator "/" cannot be calculated. I think the reason for that is because this is done on a dimension instead of measure.

Therefore, I was curious if you have any suggestions on how to overcome that issue?

[JefB]

Hi, if you share your exact code it would be easier to recommend a solution. On MichalCK's code above I'm not so sure the division in IF works as is (at least not without round brackets () around it) and I would try to do it with something like IF RESULTLOOKUP()>0 THEN DELETE([d/CustomerGroup] = "#") ENDIF

[MaxT]

Apologies @JefB, I've tried to code from @MichalCK and to give a snipped of what I've resulted in the error mentioned in my previous message:

This was the closest I came to trying his code.

So indeed the challenge I'm facing is that I have duplicated records where the record is once with Dimension "Deelneming" filled (anything else then "#") and once with it being "#". 

However, I also have cases of Dimension "Deelneming" being "#" where it doesn't contain another value. Those cases I don't want to delete but I only want to delete the cases of the duplicate records.

Hope that makes it a bit more clear, if not please let me know what additionally I need to share to give you a better idea.

[MichalCK]

Hi @MaxT,

there is an issue in your code line 3, please try to use

IF RESULTLOOKUP() / RESULTLOOKUP([d/Deelneming] = #GROOTBOEKREKENING) < 1