Page 1 of 1
MDX: retain duplicate elements in a UNION
Posted: Thu Jul 04, 2013 12:55 pm
by 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?
Re: MDX: retain duplicate elements in a UNION
Posted: Thu Jul 04, 2013 1:23 pm
by 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?
Re: MDX: retain duplicate elements in a UNION
Posted: Thu Jul 04, 2013 3:10 pm
by 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?
Re: MDX: retain duplicate elements in a UNION
Posted: Thu Jul 04, 2013 3:14 pm
by AmbPin
"All" yes that it.
Re: MDX: retain duplicate elements in a UNION
Posted: Thu Jul 04, 2013 3:16 pm
by holger_b
Thanks a lot, AmbPin.
Re: MDX: retain duplicate elements in a UNION
Posted: Thu Jul 04, 2013 11:31 pm
by 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.
Re: MDX: retain duplicate elements in a UNION
Posted: Fri Jul 05, 2013 8:06 am
by 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.