Remove duplicate elements in MDX subset build

[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.

[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]

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.

[gtonkin]

Lotsa's solution is far more elegant for what you are trying to achieve here.

[Wim Gielis]

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.

[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]

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%] } )' )));