Page 1 of 1
MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 1:13 am
by sk1080in
I have a value written in one of the lookup cubes (Example: W010215, W050115, W040115) and on the basis of first 3 characters want to create subset in another dimension which has values like W01, W02, W03....
So whatever the value is in lookup cube, should become the basis for subset in another dimension.
Can somebody guide me if this is possible.
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 1:39 am
by rmackenzie
TM1 supports the LEFT function - it may likely be un-documented and differ between versions etc.
Anyway, you can do something like this:
Code: Select all
{TM1FILTERBYPATTERN(
{TM1SUBSETALL([Interesting W Things])},
LEFT(
[Control Cube].(
[Control Parameter Dimension].[Some Parameter],
[Control Measure Dimension].[String]
),
3
) + "*"
)}
Note the concatenation of the wildcard character (*) at the end of the filter.
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 1:48 am
by sk1080in
Thanks a lot, it is working fine, however giving all levels of elements starting with the 3 characters selected. Can it be restricted to 0 Level only ?
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 1:51 am
by ardi
sk1080in wrote:Thanks a lot, it is working fine, however giving all levels of elements starting with the 3 characters selected. Can it be restricted to 0 Level only ?
You can embed the above statement with TM1FILTERBYLEVEL and that will give you only level zero elements
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 2:10 am
by EvgenyT
TM1 supports the LEFT function - it may likely be un-documented and differ between versions etc.
You are right, it's not documented anywhere.
BTW RIGHT function is supported too (not documented of course)
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 2:26 am
by sk1080in
Thanks a lot everyone, it has worked for me. Last question...Can I pick something from between as well ? Like start from 4th Character and pick 2 Characters ?
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 2:51 am
by EvgenyT
To my knowledge (and from testing) MID function is not supported in TM1 MDX.
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 6:26 am
by rmackenzie
EvgenyT wrote:To my knowledge (and from testing) MID function is not supported in TM1 MDX.
But you can achieve the output of a MID if you have LEFT, RIGHT and LEN. E.g. this will work:
Code: Select all
{TM1FILTERBYPATTERN(
{TM1FILTERBYLEVEL(
{TM1SUBSETALL([Interesting W Things])},
0
)},
LEFT(
RIGHT(
[Control Cube].(
[Control Parameter Dimension].[Some Parameter],
[Control Measure Dimension].[String]
),
LEN(
[Control Cube].(
[Control Parameter Dimension].[Some Parameter],
[Control Measure Dimension].[String]
)
) - 4 + 1
), 2
) + "*"
)}
Re: MDX , create subset by value in other cube
Posted: Tue Mar 24, 2015 10:04 pm
by sk1080in
Brilliant Solution, Thanks for that. You are a Star.