MDX: retain duplicate elements in a UNION

[holger_b]

In Plilippe Bichard's most valuable MDX Primer (http://www.pxbconsulting.com, don't know what I should do if it was not there) it says:

Union joins two sets together, returning the members of each set, optionally retaining or dropping duplicates (default is to drop).

Can anyone help me in retaining duplicate elements?

[AmbPin]

Here you go:-

Code: Select all

{
	Union(
		  TopCount( { TM1FILTERBYLEVEL( {TM1SUBSETALL( [MyDim] )}, 0)} , 5)
		, TopCount( { TM1FILTERBYLEVEL( {TM1SUBSETALL( [MyDim] )}, 0)} , 5)
		, All
	)
}	

It is actually in the primer if you read down about creating unions. Out of interest why would you want to do this?

[holger_b]

It is about CreateSubsetByMDX: In case the MDX result is empty, the creation of the subset fails. To overcome this, I would like to insert a dummy as the first element which I would then delete afterwards. But in case the dummy happens to be an element in the MDX result, I would delete an element which I may have needed, so I would rather have it show up twice in that case.

So the trick is 'All', right?

[AmbPin]

"All" yes that it.

[holger_b]

Thanks a lot, AmbPin.

[qml]

I'll just add that the following notation for adding sets also works, no need to use UNION if you don't want duplicates removed. Just separate sets with commas and put an extra pair of brackets around everything.

Code: Select all

{ {[dim].[el1]}, {[dim].[el2]} }

[AmbPin]

Another approach.
I have a short generic process that I call when I want to create a subset via MDX. This process first deletes the subset, if one already exists with that name, then creates the new subset based on the parameters with which my process was called.
In the main process, after I call my create by mdx process described above, I can look to see if any errors occurred and react accordingly.