Planning Analytics with Watson

 View Only
  • 1.  INSTR() and MDX

    Posted Thu April 21, 2022 06:37 AM

    When using the INSTR function, the comparison is typically case-sensitive unless you use the 4th argument and set the flag to 1.

    Not sure that this has been documented and when my luck will run out with this.
    Microsoft MDX documentation for argument 4 says:

    Compare
    (optional) An integer value. This argument is always ignored. It is defined for compatibility with other Instr functions in other languages.

    The questions are thus:
    a) In TM1 MDX queries, does 1 as the 4th argument mean case-insensitive search?
    b) With the stringer typing etc. anticipated, is this likely to change? If so, how?




    ------------------------------
    George Tonkin
    ------------------------------


  • 2.  RE: INSTR() and MDX

    Posted Thu April 21, 2022 08:20 AM
    Hi George,

    Funnily enough I was having the exact same thoughts, particularly with regard to question b! :)

    regards,

    Mark

    ------------------------------
    Mark Wragg
    ------------------------------



  • 3.  RE: INSTR() and MDX

    Posted Fri April 22, 2022 04:26 AM
    TM1 indeed follows more or less the VBA specification of the Instr function (which in retrospect is a bit of an unexpected choose - which predates my involvement - given the historical case and space insensitiveness of the product). I'm saying 'more-or-less' because the VBA specification by default uses 'binary' comparison, which implies case sensitiveness, whereas specifying this forth parameter as 1 implies 'text' compare which then also seems to imply case insensitive compare.

    TM1 simply converts the string parameters to lower case before doing the operation if this forth argument is set to 1 (ignoring any local specific stuff which VBA in case of text compare seems to include as well).

    Apart from improving the way we do comparison itself (read: be better about how we comparing UTF-8 encoded Unicode strings) potentially, I don't see why we would change the behavior of this Instr function itself. No changes are anticipated right now.

    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 4.  RE: INSTR() and MDX

    Posted Fri April 22, 2022 04:34 AM
    Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

    ------------------------------
    George Tonkin
    ------------------------------



  • 5.  RE: INSTR() and MDX

    Posted Sat April 23, 2022 05:55 AM
    I will add a reference to this topic since it might still be relevant:
    https://www.tm1forum.com/viewtopic.php?f=3&t=11598

    ------------------------------
    Wim Gielis
    ------------------------------



  • 6.  RE: INSTR() and MDX

    Posted Mon April 25, 2022 06:07 AM
    Relevant in the sense of what exactly Wim?

    Instr is a documented MDX function so it is not a surprise that it exists in TM1 as well I hope. The surprising part might be related to the fact that the first and the forth parameter are optional (as opposed to the last two), leaving it a bit in the middle what happens when you pass three parameters. TM1 presumes the parameters are provided in the order of the optional parameters (pretty standard for languages that don't allow specifying which parameter one is talking about) and therefore presumes if three parameters are passed it's the start index (first), text and pattern parameters. This is why all four parameters need to be specified if one wants to specify the compare type (forth) parameter as well. 

    Am I missing something else Wim?

    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 7.  RE: INSTR() and MDX

    Posted Mon April 25, 2022 02:29 PM
    Hello Hubert,

    I was referring to that topic, on Instr just as this topic, since apparently there are different results in TM1 clients for the same expression.
    I haven't tested it then and also not now, but I would think this will still be the case (for users in the Subset Editor and active forms).

    ------------------------------
    Wim Gielis
    ------------------------------



  • 8.  RE: INSTR() and MDX

    Posted Tue April 26, 2022 03:52 AM
    Hi Wim,

    None of the clients mentioned in that topic, AFAIK, interpret the MDX themselves in any way shape or form meaning they all leave execution up to TM1 itself so I'd be surprised there would be differences as such. The only thing, hence me explicitly mentioning that in my previous message, is that indeed one needs to specify the first (optional) parameter to be able to use the forth (optional) text compare parameter. The article seems to suggest at least once, if not indirectly twice, that one can just add that forth parameter while ignoring the first one, that is not the case and might have lead to confusion.

    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 9.  RE: INSTR() and MDX

    Posted Tue April 26, 2022 03:32 PM
    Thanks Hubert. I will update that topic with a cross-reference to this topic.

    ------------------------------
    Wim Gielis
    ------------------------------