This parallel post has just concluded - basically the same as your problem.
With the exception of the definitions and internal workings of certain types of database tools, a numeric field would not normally contain leading 0's since they are numerically rather pointless. Indeed a numeric Account number field is rather pointless since there would rarely if ever be any reason to apply any math to it.
In reality an Account 'number' is a character field which just happens to contain only numbers. Often the reason for that is that the simple way to provide automatic and unique account reference identification is to use an incremental numeric sequence. If people then choose to start there records from number 1 rather than, say, 100000 in a six digit field they will find the consequences you are finding when a few records have been added.
It's not wrong. It's not a problem. But it does not seem 'right' to the way we have been trained to 'see' things!
The concatenation question is simple in that you cannot concatenate numeric fields because they are tfor summing. Similarly you cannot sum character fields, but can concatenate them.
If you skipped your conversion of part of the original extract to become a numeric field you would not have had the problem of concatentation. With the original character data being, I assume, left justified (rather than right justified for a numeric field), I would guess the sort order would have been as required as well. (Although there could be some problems with that assumption under certain conditions.)
On the other hand I assume you are now entirely expert on converting character fields to numeric and vice versa - which may well be a very valuable skill for future Monarch modelling. :cool:
Even better, you have grappled with IF, LEN and STUFF and won, all of which suggests you have found, or are about to suddenly find, yourself much more comfortable looking at an input file and and seeing just how to get the results you need from it. And that is excellent as it puts you in control.
All the best.
Just to add to the conversation: There are certain cases where a truly numeric number would need to have leading zeroes. One such instance that I'm using is for our datafile for W2's. The numbers are left padded with zeroes and decimals are dropped to fullfill a data field. Thus if a data field requires 15 placesmarks, and I'm transferring an amount that's $123.45, that number gets translated to 000000000012345. Thus you have a numeric padded field that will, eventually, have math applied to it. I'm sure you can still argue that the number is character based, the zeroes are just placemarks and that the zeroes will be truncated when read into the recieving database.
Just my 000000000000002 worth.
Yep, you are quite right. If a value is truly numeric and needs to be exported in the certain format, padded with leading zeros for example, that it is quite justified. It's one of the things I sort of implied when I mentioned 'exceptions', though to be fair if the need exists it is hardly an exception!
The "Options" settings for exporting to a text file offers a standard for this, which is mentioned in the referenced thread.
The thing is though I have rarely, if ever, come across a number-only based Account Number (or similar) data field where any math was to be applied, so the normal way of thinking about such fields seems to be best in character terms with the capability to convert to numeric for specific purposes being held in reserve in case some form of math calculation is required -perhaps as part of an audit process or maybe some sort of security exercise.
So it just seem easier, most of the time, to think of the field as a character field.
We have just purchased a 6 figure software system that allows us to push reports out web based as opposed to paper (an attempt to go paperless).
With the way that the software works - - - with it's drilldown capabilities - - - it is important to keep the account and description together in one field. The software reads as text for sorting (even though the original report generated does not) so to keep things sequential is why we need to pad. In this instance where subaccounts can be from 1 - 9999 there has to be a logic formula involved too.
I consider myself an excel guru - which does not help me here - but did allow me to come up with this formula (which may not be the best way but it did work).
I just taught myself monarch last week and am being tossed into the fire as admin wants to see results - - - asap. Finding this forum has REALLY HELPED.
IF(LEN(LSplit(Item,2," ",1))<3,Stuff(LSplit(Item,2," ",1),1,0,"00"),
IF(LEN(LSplit(Item,2," ",1))<4,Stuff(LSplit(Item,2," ",1),1,0,"0"),LSplit(Item,2," ",1))))][/font][/quote]Then follow that up with:
Thanks - - - I think we are seeing, as they say, that there is more than 1 way to skin a fox
I will try using the STR function too.
Being a novice - I know what I need but just having problems getting there.
In another report today I couldn't get the other formula to work so I used: