Trim a node name till special character "-"

Hi Team,

 

Just looking for formula to trim my node name till a special character "-".

'AccountL1- 234'

I want to get only AccountL1.

 

Regards,

Usha

 

Best Answer

  • I would think the FIND formula would need to be

     

    FIND("-", 'Account L1'.Display Name, 0)

     

    David

Answers

  • @usha.cherukuri 

     

    Assumption - Account L1 is your list, if not then tweak your second parameter

     

    First you will have to figure out the place of the " -" . Use this formula in Line item Test

     

    Test = FIND("-", NAME(ITEM('Account L1')), 0) 

    Second line item Test 1 = LEFT(NAME(ITEM('Account L1')),Test-1)

     

    Hope this helps

    Misbah

  • Hi Mishab,

     

    Thanks for your rsponse, but the first formula for test

    FIND("-", NAME(ITEM('Account L1')), 0) doesn't give any result and its a numbered list dimension.

     

    the formula worked but doesnt give any value here.

     

    Regards

    Usha

  • @usha.cherukuri,

    I hope it should be the code of that list. Please try Code(Item('')) instead Name(Item('')).

  • Hi Kavin,

    Yes even I thought so, but still no result:(

    It takes the formula in but result is 0 every time, though I have special characters in member name.

    Thanks
    Usha
  • Could you please tell us how would you get the result 'AccountL1- 234'? If it is not code/name, then it should be the attribute of that list (Either property/line item). So apply your formula on that property/line item and you will get your end result. 

    For ex,

    I have a numbered list 'A' with Display Name as property(text). Then the formula should be,

    Test = FIND("-", 'A.Display name', 0) 

    Second line item Test 1 = LEFT('A.Display name',Test-1)

    Hope this helps.

    Regards,
    Kavin.

     

  • Hi David,

    Thanks this now works for child node in AccountL1 dimension perfectly.

    Regards
    Usha

  • Hi David,

    This is working good for Child Level, but Parent Level for 'AccountL1' is 'AccountL0' which is not a number list, for that the value is showing zero.
    Is there any way how we can get the parent level also populated here?

    Thanks
    Usha
  • The challenge you have here is the line item is dimensioned by Account L1 and in a composite hierarchy Account L0 is a calculation

    So we need to build a couple of extra line items

    1. You need to know if the item is a L1 or L0, so set up a Boolean line item called Child? and set this to TRUE

    2. Create another line item that brings in the Parent of the L1 - in your model it will be something like PARENT((ITEM('Account L1')).  It is always best to have this in a separate line item (You will likely use it again).  Remember "calculate once, reference many times" 

    3. Create a text line item for the name of A0, but set the summary option to 'Last non-blank'

    4. This is the existing line item we just solved for

    5. Now you bring it all together with a conditional and set this summary option to 'Formula'

    2019-11-25_10-33-15.png2019-11-25_10-33-54.png

    Viola!

    David

  • @usha.cherukuri 

     

    Probably you might have to add two more line items. Here is how I achieved this.

     

    Added Test 3  Line item where I checked Parent Of the List, kept the Summary as Last Non Blank

     

    clipboard_image_1.png

     

    Added Line item Test 4 - Kept the summary as Formula

     

    clipboard_image_2.png

     

    clipboard_image_3.png

     

    One thing to note that you will have to change the Summary of the first line item as well - Change it to Formula

    clipboard_image_0.png

     

    Thanks,

    Misbah