Page 1 of 1
Remove duplicate elements from the subset
Posted: Mon Nov 23, 2020 6:07 pm
by 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!
Re: Remove duplicate elements from the subset
Posted: Mon Nov 23, 2020 6:31 pm
by 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.
Re: Remove duplicate elements from the subset
Posted: Tue Nov 24, 2020 6:45 am
by 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;
Re: Remove duplicate elements from the subset
Posted: Tue Nov 24, 2020 10:57 am
by 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()} ) }
Re: Remove duplicate elements from the subset
Posted: Tue Nov 24, 2020 11:06 am
by Wim Gielis
Indeed, good catch !
I forgot, even when I do this all the time manually in the Subset Editor !
Re: Remove duplicate elements from the subset
Posted: Tue Nov 24, 2020 1:42 pm
by 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.
Re: Remove duplicate elements from the subset
Posted: Tue Nov 24, 2020 4:29 pm
by 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)
Re: Remove duplicate elements from the subset
Posted: Tue Nov 24, 2020 6:37 pm
by Wim Gielis
pandeytm777 wrote: ↑Tue Nov 24, 2020 1:42 pm
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.