Remove duplicate elements in MDX subset build

Post Reply
Paul-TM1
Posts: 124
Joined: Tue Jun 13, 2017 3:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Remove duplicate elements in MDX subset build

Post by Paul-TM1 »

Hi all,
I am trying to build an MDX subset in the following way. I want 'Jan Forecast' and 'Mar Forecast' always included in the subset. 'PriorMonthSnapshot_1' refers to a snapshot taken 2 months ago and 'PriorMonthSnapshot_2' refers to a snapshot taken 3 months ago. There is a chance that the elements can get duplicated and I want to remove the dupes. Can someone help me? Any ideas???

Here's part of the code:

Code: Select all

	ElseIf (DimName @= 'Versions' );
		SubsetCreate(dDimName,sSubName);
		SubsetElementInsert(dDimName,sSubName, 'Planning', 1);
		SubsetElementInsert(dDimName,sSubName, 'Budget', 1);
		SubsetElementInsert(dDimName,sSubName, PriorMonthForecast, 1);
		SubsetElementInsert(dDimName,sSubName,'Jan Forecast',1);
		SubsetElementInsert(dDimName,sSubName,'Mar Forecast',1);
		SubsetElementInsert(dDimName,sSubName,PriorMonthSnapshot_1,1);
		SubsetElementInsert(dDimName,sSubName, PriorMonthSnapshot_2,1);
	EndIf;
Thanks,
Paul.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Remove duplicate elements in MDX subset build

Post by gtonkin »

If you are building vi TI there are any number of ways to do this.
Simplest is probably to add the elements to a string and scan the string.
If you are about to add an element check if it is in your string i.e. previously added. If not add otherwise skip.

You could also build an MDX string - add the elements to yield a string like the below then set your set's MDX to the string:

Code: Select all

DISTINCT({[Customer].[10021304], [Customer].[10020926], [Customer].[10023364], [Customer].[10023323],[Customer].[10021304]})
Alternatively you could use SubsetElementExists to test if the element exists in the set.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Remove duplicate elements in MDX subset build

Post by lotsaram »

For a relatively small subset just use MDX with the + (union) operator between the elements. This will automatically do a distinct. I think this would be the most simple.

Code: Select all

SubsetMDXSet(dDimName, sSubName, Expand('{{[%dDimName%].[Planning]}+{[%dDimName%].[Budget]}+{[%dDimName%].[%PriorMonthForecast%]}+{[%dDimName%].[Jan Forecast]}+{[%dDimName%].[%PriorMonthForecast%]}+{[%dDimName%].[Mar Forecast]}+{[%dDimName%].[%PriorMonthForecast%]}+{[%dDimName%].[%PriorMonthSnapshot_1%]}+{[%dDimName%].[%PriorMonthForecast%]}+{[%dDimName%].[%PriorMonthSnapshot_2%]}}'));
Also FYI if the position argument in SubsetElementInsert is 0 this is shorthand for "add at last index" which makes it much easier to control the order.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Remove duplicate elements in MDX subset build

Post by gtonkin »

Lotsa's solution is far more elegant for what you are trying to achieve here.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Remove duplicate elements in MDX subset build

Post by Wim Gielis »

Paul-TM1 wrote: Tue May 23, 2023 8:21 pm Hi all,
I am trying to build an MDX subset in the following way. I want 'Jan Forecast' and 'Mar Forecast' always included in the subset. 'PriorMonthSnapshot_1' refers to a snapshot taken 2 months ago and 'PriorMonthSnapshot_2' refers to a snapshot taken 3 months ago. There is a chance that the elements can get duplicated and I want to remove the dupes. Can someone help me? Any ideas???

Here's part of the code:

Code: Select all

	ElseIf (DimName @= 'Versions' );
		SubsetCreate(dDimName,sSubName);
		SubsetElementInsert(dDimName,sSubName, 'Planning', 1);
		SubsetElementInsert(dDimName,sSubName, 'Budget', 1);
		SubsetElementInsert(dDimName,sSubName, PriorMonthForecast, 1);
		SubsetElementInsert(dDimName,sSubName,'Jan Forecast',1);
		SubsetElementInsert(dDimName,sSubName,'Mar Forecast',1);
		SubsetElementInsert(dDimName,sSubName,PriorMonthSnapshot_1,1);
		SubsetElementInsert(dDimName,sSubName, PriorMonthSnapshot_2,1);
	EndIf;
Thanks,
Paul.
Is this an MDX subset ? It seems rather static to me and not really MDX.

More importantly, what happens to the subset after this code has run ?
Is it a subset for consumption in the model ? In which case the 0 index that Lotsa is referring to makes more sense than index 1.
Is it a subset in the data source if a TI process ? I should check but I thought that the duplicate elements would be skipped anyway.
It’s been a couple of years since I looked at it and I may have to refresh my memory and redo the test.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Paul-TM1
Posts: 124
Joined: Tue Jun 13, 2017 3:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Remove duplicate elements in MDX subset build

Post by Paul-TM1 »

Thank you for your responses. I could not get the union work with Lotsa's expression. I instead used SubsetElementExists function and finished it.
I did like to know how to add union to the mdx in the case. Can someone please let me know?

Thanks,
Paul.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Remove duplicate elements in MDX subset build

Post by Wim Gielis »

Hi Paul,

Here is a full code listing showing 6 subset definitions:
  • Paul - slightly improved code but returning duplicates
  • Lotsa - operator + to return a distinct list
  • Wim - function Union to return a distinct list works BUT remember that Union only works on pairs
  • George - function Distinct to return a distinct list
  • George - condensed coding but with a double Expand
  • George - even more condensed coding
You may choose which variant you want to use.

Code: Select all

dDimName = 'Versions';

PriorMonthForecast = 'Mar Forecast';
PriorMonthSnapshot_1 = 'Mar Forecast';
PriorMonthSnapshot_2 = 'Jan Forecast';

# Paul - slightly improved code but returning duplicates
sSubName = '1 - Paul';

SubsetCreate( dDimName, sSubName );

SubsetElementInsert( dDimName, sSubName, 'Planning', 0 );
SubsetElementInsert( dDimName, sSubName, 'Budget', 0 );
SubsetElementInsert( dDimName, sSubName, PriorMonthForecast, 0 );
SubsetElementInsert( dDimName, sSubName, 'Jan Forecast', 0 );
SubsetElementInsert( dDimName, sSubName, 'Mar Forecast', 0 );
SubsetElementInsert( dDimName, sSubName, PriorMonthSnapshot_1, 0 );
SubsetElementInsert( dDimName, sSubName, PriorMonthSnapshot_2, 0 );


# Lotsa - operator + to return a distinct list
# [There were too many elements in the list compared to Paul's example]
sSubName = '2 - Lotsa';

SubsetCreate( dDimName, sSubName );

SubsetMDXSet( dDimName, sSubName, Expand( '{{[%dDimName%].[Planning]}+{[%dDimName%].[Budget]}+{[%dDimName%].[%PriorMonthForecast%]}+{[%dDimName%].[Jan Forecast]}+{[%dDimName%].[Mar Forecast]}+{[%dDimName%].[%PriorMonthSnapshot_1%]}+{[%dDimName%].[%PriorMonthSnapshot_2%]}}' ));


# Wim - function Union to return a distinct list works BUT remember that Union only works on pairs
sSubName = '3 - Wim';

SubsetCreate( dDimName, sSubName );

SubsetMDXSet( dDimName, sSubName, Expand( 'Union( {[%dDimName%].[Planning]}, { {[%dDimName%].[Budget]}, {[%dDimName%].[%PriorMonthForecast%]}, {[%dDimName%].[Jan Forecast]}, {[%dDimName%].[Mar Forecast]}, {[%dDimName%].[%PriorMonthSnapshot_1%]}, {[%dDimName%].[%PriorMonthSnapshot_2%]} } )' ));


# George - function Distinct to return a distinct list
sSubName = '4 - George';

SubsetCreate( dDimName, sSubName );

SubsetMDXSet( dDimName, sSubName, Expand( 'Distinct( { {[%dDimName%].[Planning]}, {[%dDimName%].[Budget]}, {[%dDimName%].[%PriorMonthForecast%]}, {[%dDimName%].[Jan Forecast]}, {[%dDimName%].[Mar Forecast]}, {[%dDimName%].[%PriorMonthSnapshot_1%]}, {[%dDimName%].[%PriorMonthSnapshot_2%]} } )' ));


# George - condensed coding but with a double Expand
sSubName = '4 - George - condensed';
d = '[%dDimName%].';

SubsetCreate( dDimName, sSubName );

SubsetMDXSet( dDimName, sSubName, Expand( Expand( 'Distinct( { {%d%[Planning]}, {%d%[Budget]}, {%d%[%PriorMonthForecast%]}, {%d%[Jan Forecast]}, {%d%[Mar Forecast]}, {%d%[%PriorMonthSnapshot_1%]}, {%d%[%PriorMonthSnapshot_2%]} } )' )));


# George - even more condensed coding
sSubName = '4 - George - even more condensed';
d = '[%dDimName%].';

SubsetCreate( dDimName, sSubName );

SubsetMDXSet( dDimName, sSubName, Expand( Expand( 'Distinct( { %d%[Planning], %d%[Budget], %d%[%PriorMonthForecast%], %d%[Jan Forecast], %d%[Mar Forecast], %d%[%PriorMonthSnapshot_1%], %d%[%PriorMonthSnapshot_2%] } )' )));
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply