Contributor

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

 

10 REPLIES 10
Community Boss

Re: Trim a node name till special character "-"

@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

Contributor

Re: Trim a node name till special character "-"

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

Community Boss

Re: Trim a node name till special character "-"

@usha.cherukuri,

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

Contributor

Re: Trim a node name till special character "-"

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
Community Boss

Re: Trim a node name till special character "-"

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.

 

Community Boss

Re: Trim a node name till special character "-"

I would think the FIND formula would need to be

 

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

 

David

Contributor

Re: Trim a node name till special character "-"

Hi David,

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

Regards
Usha

Contributor

Re: Trim a node name till special character "-"

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
Highlighted
Community Boss

Re: Trim a node name till special character "-"

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