We just changed GL systmes and now most of our Cost Centers (those that are 100% numeric values) come out of the system with leading zeros.  This can be 4, 3, 2 or no leading zeros.  For example:

From our original system we had been getting:

2601

26006

260010

5D69

100011J2

Our new system now give us:

00002601

00026006

00260010

00005D69

100011J2

Our Cost Center field is a Label by definition in addition to the fact that a few of our Cost Centers have alpha characters.

Ideally I need to be able to do a Value Override formula that will remove the leading zeros when they exist but otherwise leave the value unaffected

Does anyone know a function/formula that can resolve this dilemma?

Thanks,

Steve

Hi Steve,

The only option I can think of right now is doing a lookup/mapping table. In other words create a table with the new and old cost centers side by side. Then do a lookup based on the new cost center to bring back the old format. Then use that coloumn to map to master data sets etc for consistency across time.

Illustrated below:

New CC      Old CC

00002601   2601

00026006   26006

00260010   260010

00005D69  5D69

100011J2   100011J2

New lookup coloumn: Lookup(Cost Center,Mapping File,New CC,Old CC)

This will also be a check to see if you introduce any new Cost Centers as nothing will be brought back in your lookup coloumn.

Please let me know if you need any clarification or have any follow up questions,

Lauren Griessel

If only numbers are present in the string, it's a simple matter of converting the string to a number then back to a string. For example...

1. Create a new column with Type set to "Numeric" and its value set to the Label column containing the leading zeros.
2. After saving the table, you'll notice your values have dropped the leading zeros. However, because it's a numeric column it will sum the values.
3. To treat the values (without the leading zeros) as labels, create another column with Type set to "Label" and its value set to the Numeric column created in step 1.

Alternatively...you could overcome it within the same column or, to ease troubleshooting, a new Label column with Value set to something like...

=If(Left(Leading Zero Label,3)="000",Right(Leading Zero Label,Len(Leading Zero Label)-3),If(Left(Leading Zero Label,2)="00",Right(Leading Zero Label,Len(Leading Zero Label)-2),If(Left(Leading Zero Label,1)="0",Right(Leading Zero Label,Len(Leading Zero Label)-1),Leading Zero Label)))

If you need to extend accounting for more leading zeros than three occurrences, just amend the formula accordingly. =)

I did just that and it comes back with the leading zeros in my final column.

Expense Center                Expense Center Numeric             Expense Center Clean

00002601                                                                   2601 00002601

Expense Center Numeric is a Numeric field and = Expense Center.

Expense Center Clean is a Label and = Expense Center Numeric

I even tried doing the same in a Generated table off the GL with Expense Center as the PK and the only field in the table then creating the two transformed columns and get the same result.  The idea was to have a lookup table which someone else proposed (but automated).  I would like to automate that so I don’t have to manually maintain it if new Expense Centers appear.

Hi Steve,

If you have a new expense center coming in you would have to add it to your chart of accounts in order for it to flow up your model via cost pools and cost sub pools etc. Thus, having this table to help see when this happens could be very helpful.

Thanks,

Lauren Griessel

Steve, I would create a transform of that dataset and then convert the column back to label, I think that way you might get rid of the leading zeros. Did not test myself though, just guessing it might work.

Aydan

I, too, thought about the same thing, but in my testing learned that approach would only work with text strings containing numbers only. Since @Steve Conklin's sample data included non-numerics, too, he'd have to go with @Lauren Griessel's approach (which will require some regular review) or account for the leading zeros with a nested IF approach.

If the data is being sourced from a database or other reporting engine, it may be possible to modify the data source more easily than Apptio while also maintaining overall performance. =)

Have you tried the function NumberFormat()? It converts numbers in Label columns to a specified format

Or the RIGHT() function?  We have six-digit cost centers that come through with a leading zero sometimes and to strip that out we use "=RIGHT(ColumnName,6)" for example.

Meh, nix that.  I was bad and didn't read the original string.  You don't have a set amount of leading zeroes, so that wouldn't work.  I'm leaning towards Lauren's idea of a lookup/mapping table.  You would definitely have to add that to your monthly processes - checking for new entries to add to the table...

Not necessary to add to the monthly processes if the matter can be addressed with removal of the leading zeros via formula. Check our the nested IF function approach mentioned earlier in the thread. Of course, "if" more attention on the new cost centers is desired...then the monthly process approach is certainly the way to go. ;-)

Hi Steve,

As long as your cost centers are always 8 characters long and the leading zeros do not exceed 4 characters then this should work as an override:

=IF(Search("0000",$_)=1,Right($_,4),IF(Search("000",$_)=1,Right($_,5),IF(Search("00",$_)=1,Right($_,6),IF(Search("0",$_)=1,Right($_,7),$_))))

In the example values, one of them did not start with a zero...

100011J2

Thus, the example formula is flawed. Further, using the Search function will search for any occurrence of the zero string indicated in the function's argument...risking potential error. It's better to check for leading zeros using the Left function and, if found and like you did, use the Right function.

Hi Matt,

I tested the formula before posting it.  The last '$_' is where the formula will use the original value if there are no leading zeros.  Also, I am comparing the returned value of the Search function to 1 which is left most location in the string.

I do like your use of Len().  Here is an updated version of the formula that incorporates it:

=IF(Search("0000",$_)=1,Right($_,Len($_)-4),IF(Search("000",$_)=1,Right($_,Len($_)-3),IF(Search("00",$_)=1,Right($_,Len($_)-2),IF(Search("0",$_)=1,Right($_,Len($_)-1),$_))))

This formula will work with strings of any length and handle up to 4 leading zeros.

Hi5

Yep....I misread your formula where it was checking for a value of one for the Search function's return value. That specific piece avoids the risk I was referring to. ;-)

Generally, our two approaches are very similar. However, I argue the Search function is more performance intense than using the Left function (see my proposed formula in earlier comments where you could replace field Leading Zero Label with $_). The Search function would look at the whole string versus the Left function looking at the values specified by its second argument.

You've got a point there.  Using Left() for the override function with '$_' would look like this:

=If(Left($_,4)="0000",Right($_,Len($_)-4),If(Left($_,3)="000",Right($_,Len($_)-3),If(Left($_,2)="00",Right($_,Len($_)-2),If(Left($_,1)="0",Right($_,Len($_)-1),$_))))

I've tested it and it is fully functional.

This would be a great button or function to have, someone submit this as an RFE. 

Stumbled upon this while deduping transactions for a customer. NUMBERFORMAT() *does* work in this scenario (as suggested by @Manik Patil quite some time ago):

My client's scenario: trying to match up document numbers across incoming GL data and a separate mapping data set for exclusion of project costs in the GL.

   Mapping data set has "document number" without leading zeros.

   GL data set has "document number" WITH leading zeros; string length up to 10 characters including leading zeros.

Steps:

      1) In both data sets, I've set the incoming "document number" column to type Label.

      2) In the GL data set (where leading zero issue exists), I create a new column of type Label:
         doc num minus zeros =NUMBERFORMAT(document number, "##########")

      3) I proceed to do my lookup:
         Is Project Cost HELPER =LOOKUP(doc num minus zeros, SRC MAP doc nums, document number, retval)