Platform

 View Only

 Apptio function for Repeat Text a certain number of Times

Jump to  Best Answer
  • TBMStudio
Apptio Community Member's profile image
Apptio Community Member posted Tue March 14, 2023 04:05 AM

Hi,

The Cost centers that are getting uploaded into Apptio in one of the tables are getting converted to numbers which removes the preceding "0", resulting in CC with varied lengths of 8, 9 and 10.

While the obvious solution is to fix the source (csv issues), I am looking for a quick fix for a faster turn around till the source issues are resolved.

Just like how Excel has the repeat function REPT(), my idea is to look for a similar function in Apptio, where I can do = REPT("0", 10 - Len(CC)). This will basically append a single "0" to the CCs with a length of 9, and "00" to the CCs with a length of 8 and nothing to the CCs with a length of 10.

Does this function exist in Apptio? If not, what would be the best way to solve this? The last resort is nested IFs which I am trying to avoid.


#TBMStudio
Alex Edwards's profile image
Alex Edwards posted Tue March 14, 2023 05:26 AM  Best Answer

Hi Saaraunsh,

One solution to this could be to use the 'Numberformat' function. My example below is to create a new column (or you can edit the existing column depending on your needs), which ensures the format will contain 10 characters, based on the number of "0's" in the formula.

The Cost Center column is uploaded data. 

There is more information about the NumberFormat function in the Help Center on the below link: 

https://help.apptio.com/en-us/studio/formulas-and-functions/functions/numberformat.htm

Hopefully that helps but please do come back with questions if you have any.

Thanks,

Alex


#TBMStudio
Apptio Community Member's profile image
Apptio Community Member posted Tue March 14, 2023 08:37 AM

@Alex Edwards Excellent! Thank you :)


#TBMStudio
Mohamed Buhari S's profile image
Mohamed Buhari S posted Wed March 15, 2023 12:43 AM

Hi,

My recommendation is to go to edit mode for the Cost Centre column and try the formula below and changing type as Label

=If(left(Cost Centre,1)=0,Cost Centre,"0"&Cost Centre)

Based on the fact that some of our Cost Centre start with "0" and some do not, this worked for me. And the formatting error that ignored "0" had an impact on my lookup values depending on Cost Centre. I gave it a try, and it was successful for me.

One other thing is you have to change the column type to Label from numeric across dataset.


#TBMStudio
Guillermo Cuadrado's profile image
Guillermo Cuadrado posted Fri March 17, 2023 07:07 AM

I had struggled with a similar concept a while ago. @Alex Edwards

You could also try forcing the column to be "A" in the Import step and then edit its contents with something like

CC =NumberFormat($_,"0000000000").


#TBMStudio
Kelly Parkinson's profile image
Kelly Parkinson posted Fri December 29, 2023 09:54 PM

I think we have a similar situation, this is an example of the formula I like to use (cost centers are 4 digits): 


#TBMStudio

Related Content