TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Remove duplicate elements in MDX subset build

https://www.tm1forum.com/viewtopic.php?t=16476

Page 1 of 1

Remove duplicate elements in MDX subset build

Posted: Tue May 23, 2023 8:21 pm
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.

Re: Remove duplicate elements in MDX subset build

Posted: Wed May 24, 2023 5:47 am
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.

Re: Remove duplicate elements in MDX subset build

Posted: Wed May 24, 2023 7:23 am
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.

Re: Remove duplicate elements in MDX subset build

Posted: Wed May 24, 2023 8:38 am
by gtonkin
Lotsa's solution is far more elegant for what you are trying to achieve here.

Re: Remove duplicate elements in MDX subset build

Posted: Wed May 24, 2023 11:30 pm
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.

Re: Remove duplicate elements in MDX subset build

Posted: Fri May 26, 2023 6:59 pm
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.

Re: Remove duplicate elements in MDX subset build

Posted: Sat May 27, 2023 12:50 am
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%] } )' )));
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited