Remove duplicate elements from the subset

[pandeytm777]

Hi,
I have a requiement where dimension (pProduct) and subset name (pPorductSubset) are being passed as parameters to a TI process. The subset contains duplicate elements as shown below. How do i delete all the duplicate elements (not distinct) ? In the below example, the requirement is to remove the elements for Banana and Orange which are appearing twice in the subset. Another ask is to code it only using the prolog. Is it possible to do this ?

Input pProductSubset
---------------------h
Mango
Banana
Orange
Apple
Banana
Guava
Orange

Expected Output for the pProductSubset:
--------------------------------------------------------
Mango
Apple
Guava

Thank you!

[Wim Gielis]

What code do you come up with ? Then helpers can have a look at it and suggest improvements.

I would think that the functions SubsetGetSize, SubsetGetElementName, SubsetElementDelete in a double loop will be what you need.
Or, go the MDX route and use TM1SubsetToSet and Distinct and 1 loop.

[Wim Gielis]

Untested code.

Code: Select all

sDim = pProduct; sSub = pProductSubset;
n1 = SubsetGetSize( sDim, sSub );
While( n1 >= 1 );

   sEl1 = SubsetGetElementName( sDim, sSub, n1 );

   n2 = n1 - 1;
   While( n2 >= 1 );
   
      sEl2 = SubsetGetElementName( sDim, sSub, n2 );
      If( Dimix( sDim, sEl1 ) = Dimix( sDim, sEl2 ));
         SubsetElementDelete( sDim, sSub, n1 );
         Break;
      EndIf;
   
      n2 = n2 - 1;
   End;

   n1 = n1 - 1;
End;

[David Usherwood]

I recall that Hierarchy Sort has the useful side effect of removing duplicates - and it is supported by the MDX Record Expression feature:

Code: Select all

{ HIERARCHIZE( {TM1SubsetBasis()} ) }

[Wim Gielis]

Indeed, good catch !

I forgot, even when I do this all the time manually in the Subset Editor !

[pandeytm777]

Hi Wim Gielis,
Thank you for your reply. I used the similar logic. Your code and my code produces distinct list of elements. But I want to delete all the elements that are appearing more than once. If you look at the example I provided in my original post, elements "Banana" and "Orange" appear twice and I want to delete both the occurrences of that element. To summarize, "banana" and "Orange" should not be part of the subset at all after the TI process is run as shown in my expected output. I can use DimensionElementDelete. But it deletes the element from the dimension itself. I want to delete it only form the subset.

[PavoGa]

TM1TupleSize maybe?

This code will return the unique leaf elements of the subset:

Code: Select all

TM1FilterByLevel(FILTER( [pProduct].[pProductSubset],
		TM1TupleSize(INTERSECT([pProduct].[pProductSubset], {[pProduct].currentmember}, ALL).item(1)) = 0),
		0)

To find those that exist at least twice:

Code: Select all

TM1FilterByLevel(FILTER( [pProduct].[pProductSubset],
		TM1TupleSize(INTERSECT([pProduct].[pProductSubset], {[pProduct].currentmember}, ALL).item(1)) = 1),
		0)

[Wim Gielis]

Hi Wim Gielis,
Thank you for your reply. I used the similar logic. Your code and my code produces distinct list of elements. But I want to delete all the elements that are appearing more than once. If you look at the example I provided in my original post, elements "Banana" and "Orange" appear twice and I want to delete both the occurrences of that element. To summarize, "banana" and "Orange" should not be part of the subset at all after the TI process is run as shown in my expected output. I can use DimensionElementDelete. But it deletes the element from the dimension itself. I want to delete it only form the subset.

So you still have 2 options. The MDX way as shown above, or looping subset elements. If you came up with similar code as I did then I’m sure you could tweak it to remove all instances of the duplicate elements.