LEN works in Architect, not PAW

Post Reply
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

LEN works in Architect, not PAW

Post by PavoGa »

This code:

Code: Select all

FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
    LEN([Organization].currentmember.Name) > 18);
works just fine in Architect and TIs.

Returns "Invalid MDX expression" in PAW. The Reference Guide does not list LEN as being a supported function so there is that. Has anyone else has seen this issue and, if so, are there workarounds?
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: LEN works in Architect, not PAW

Post by Wim Gielis »

Hello,

You could fill up an attribute with the length of the string, probably with TI but a rule is possible too. After that you can query on the attribute. I haven’t used it in Workspace until now.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
PlanningDev
Community Contributor
Posts: 349
Joined: Tue Aug 17, 2010 6:31 am
OLAP Product: Planning Analytics
Version: 2.0.5
Excel Version: 2016

Re: LEN works in Architect, not PAW

Post by PlanningDev »

Couple of things here

1. Im able to make it work with PAW 39. However you may want to add the extra set of [] to force the Hierarchy name just in case you use hierarchies.

Code: Select all

FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
    LEN([Organization].[Organization].currentMember.Name) > 18);
2. If you are looking to filter on an alias you may need to make a text attribute to fill in the alias values that are blank. Its possible for alias values that have not been set to be blank in the }ElementAttributes control cube for that dimension. If that is the case for any elements then filtering on them by length becomes an issue since they are evaluated as 0 length.

3. If you want to filter by an attribute you can also shortcut that with TM1 (Assuming you don't have name conflicts with elements)

Code: Select all

FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
    LEN([Organization].[Organization].[Attribute Name Here]) > 18);
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: LEN works in Architect, not PAW

Post by PavoGa »

Wim Gielis wrote: Wed Mar 20, 2019 7:31 pm Hello,

You could fill up an attribute with the length of the string, probably with TI but a rule is possible too. After that you can query on the attribute. I haven’t used it in Workspace until now.
Squat. Forgot about that, Wim, thanks! It'll do.
Ty
Cleveland, TN
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: LEN works in Architect, not PAW

Post by lotsaram »

But don't forget that member properties are explicitly strings and TM1's MDX implementation doesn't allow type recasting. So if you want to treat the value as numeric then you need to query the ElementAttributes cube not get the member property.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: LEN works in Architect, not PAW

Post by PavoGa »

PlanningDev wrote: Wed Mar 20, 2019 9:44 pm Couple of things here

1. Im able to make it work with PAW 39. However you may want to add the extra set of [] to force the Hierarchy name just in case you use hierarchies.

Code: Select all

FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
    LEN([Organization].[Organization].currentMember.Name) > 18);
2. If you are looking to filter on an alias you may need to make a text attribute to fill in the alias values that are blank. Its possible for alias values that have not been set to be blank in the }ElementAttributes control cube for that dimension. If that is the case for any elements then filtering on them by length becomes an issue since they are evaluated as 0 length.

3. If you want to filter by an attribute you can also shortcut that with TM1 (Assuming you don't have name conflicts with elements)

Code: Select all

FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
    LEN([Organization].[Organization].[Attribute Name Here]) > 18);
Thanks. We tried all this, but apparently our version of PAW is having none of it.
Ty
Cleveland, TN
Post Reply