Getting Rid of Leading Zeroes in Character Field?
Altair EmployeeHello all,
I am using Monarch 13 Classic to pull contract IDs from one report and match it to another with $ amounts. The problem is, on one report there are leading zeroes before some of the IDs and in the other report there isn't so when I go into DPS to match the tables together by Contract ID, it won't match them since it reads it as different. (In my example below it would not match 00000003210 from A to 3210 from B for instance). I cannot change it to a number field because some contract IDs are not numeric and start with letters. Ex:
| Report A Contract ID | Amount | Report B Contract ID | Amount |
|---|---|---|---|
| AB0056789 | 100 | AB0056789 | 100 |
| X005324675 | 200 | X005324675 | 200 |
| 0000003210 | 300 | 3210 | 300 |
| 0000023456 | 400 | 23456 | 400 |
| 1115678 | 500 | 1115678 | 500 |
Where Report A is formatted differently than Report B and they are obviously not yet in the same table. I have tried creating a few different Filter Formulas to get rid of the leading 0's but they always return an error of some kind. Here is an example of one that I have tried:
IF(LEFT([Report A ContractID],1)=0,Val([Report A ContractID]),[Report A ContractID])
Any help with making a formula for this or if I am just missing something simpler, as I am new to this program and mostly self taught, please let me know.
Find more posts tagged with
Altair EmployeeThank you! I used it in a Calculated Field instead of a Filter as it would not accept it as a filter for whatever reason, and then also added in the InTrim Function to clean up the leading spaces that were there from the initial formula:
IF(LEFT([Report A Contract],1)="0",InTrim(STR(Val([Report A Contract]))),[Report A Contract])
Thanks again!
Jon
Altair EmployeeThank you! I used it in a Calculated Field instead of a Filter as it would not accept it as a filter for whatever reason, and then also added in the InTrim Function to clean up the leading spaces that were there from the initial formula:
IF(LEFT([Report A Contract],1)="0",InTrim(STR(Val([Report A Contract]))),[Report A Contract])
Thanks again!
Jon
Hi Jon,
Sorry, it didn't register with me that you were using a filter. I sort of skipped that bit I think.
As you found a calculated field and then use the new field rather than the old one is the way to go.
Was your new field left justified? If so I'm slightly surprised that you needed any TRIM function but that said it's not such a bad idea to make use of TRIMMING functions to ensure the most complete control over the data.
If you deal with a lot of data sources that are similar you now have knowledge of a powerful tool to speed your work. There are a few other Functions that do similar things with text based requirements (and numbers but the text may be more challenging) that are worth discovering and filing away for future use.
Good to know you got where you needed to be.
Grant
Altair EmployeeThank you! I used it in a Calculated Field instead of a Filter as it would not accept it as a filter for whatever reason, and then also added in the InTrim Function to clean up the leading spaces that were there from the initial formula:
IF(LEFT([Report A Contract],1)="0",InTrim(STR(Val([Report A Contract]))),[Report A Contract])
Thanks again!
Jon
I know this issue is resolved, but just to add to the conversation, you can also use this formula, which uses regex to blank out any leading zeros and leaves everything else intact:
RegexReplace ( [Report A Contract ID], "^0+", "" )
IF(LEFT([Report A ContractID],1)="0",STR(Val([Report A ContractID])),[Report A ContractID])
I have not checked this but something like the above will probably work.
Note that the 0 is from a CHARACTER field and so the first char check needs the value to be in quotes.
If you VAL the text string and STR it again is should drop the leading zeros.
Of any of the genuine character values in the Contract ID field happen to start with a zero things may need to be made a little more complicated.
Try the suggestion ans see what happens - it fits with the how close you got if it works.