<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE>.hmmessage P {
PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px
}
BODY.hmmessage {
FONT-SIZE: 10pt; FONT-FAMILY: Verdana
}
</STYLE>
<META content="MSHTML 6.00.2900.3157" name=GENERATOR></HEAD>
<BODY class=hmmessage>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009>Peter, and all the rest of you Wise Powerhousers out
there ....</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009>What Peter is suggesting will work, although my style
would be as follows (this works, I tested it):</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009> I put the value
"1,234,567.89
" (note the trailing spaces) into a file named "g"</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009> cat g | sed
's/,//g'</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009>This converts the number
wonderfully.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009>One issue to watch with would be that this would strip
ALL commas out of your input file and it's assuming that the records are
delimited by a Carriage Return.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009>I didn't attempt to do Peter's really slick looking
Powerhouse code because I'm not sure what kind of overhead would be generated by
placing this into Powerhouse.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=3><SPAN
class=869074416-03092009>If there's an issue with other fields having commas
that you don't want to kill, then awk code like this will
work:</SPAN></FONT></DIV><FONT face=Arial><SPAN class=869074416-03092009>
<P dir=ltr align=left><FONT face=r_ansi color=#0000ff></FONT> </P>
<P dir=ltr align=left><FONT color=#0000ff size=3>cat g.awk</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3>BEGIN { FS = "|"; OFS="|"
}</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3>{</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff><FONT size=3><SPAN
class=869074416-03092009> </SPAN>MFGP = ""</FONT></FONT></P>
<P dir=ltr align=left><FONT color=#0000ff><FONT size=3><SPAN
class=869074416-03092009> </SPAN>for (i=1; i<= 20;
++i)</FONT></FONT></P>
<P dir=ltr align=left><FONT color=#0000ff><FONT size=3><SPAN
class=869074416-03092009>
</SPAN>{</FONT></FONT></P>
<P dir=ltr align=left><FONT color=#0000ff><FONT size=3><SPAN
class=869074416-03092009> </SPAN>if (
substr($2,i,1) != "," && substr($2,i,1) != " " )</FONT></FONT></P>
<P dir=ltr align=left><FONT color=#0000ff><FONT size=3><SPAN
class=869074416-03092009>
</SPAN>MFGP = MFGP toupper(substr($2,i,1))</FONT></FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3>}</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3>print $1, $2, $3, $4, "
Re-formatted field 2 ", MFGP</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3>}</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3></FONT> </P>
<P dir=ltr align=left><FONT color=#0000ff size=3></FONT> </P>
<P dir=ltr align=left><FONT><FONT color=#0000ff><FONT size=3><SPAN
class=869074416-03092009>Here is our input file:</SPAN> cat
/tmp/glw/g1</FONT></FONT></FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3>a text field, with commas,
needs them|1,234,569.89 |1234|test</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3></FONT> </P>
<P dir=ltr align=left><SPAN class=869074416-03092009><FONT color=#0000ff
size=3>So, we have 4 fields, delimited with pipes. Now, we process that
through awk, being very careful to do our "comma processing" on the CORRECT
field!</FONT></SPAN></P>
<P dir=ltr align=left><FONT size=3></FONT> </P>
<P dir=ltr align=left><FONT color=#0000ff size=3>cat /tmp/glw/g1|awk -f
g.awk</FONT></P>
<P dir=ltr align=left><FONT color=#0000ff size=3></FONT> </P>
<P dir=ltr align=left><SPAN class=869074416-03092009><FONT color=#0000ff
size=3>Gives the following result:</FONT></SPAN></P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<P><FONT color=#0000ff size=3>a text field, with commas, needs
them|1,234,569.89 |1234|test|Re-formatted field 2 |1234569.89</FONT></P>
<P><FONT color=#0000ff size=3></FONT> </P></BLOCKQUOTE>
<P dir=ltr align=left><SPAN class=869074416-03092009><FONT color=#0000ff
size=3>I know there's likely a more elegant way to strip the commas/spaces out,
I'm a poor COBOL boy at heart, so I brute-forced it, but this WILL
work.</FONT></SPAN></P>
<P dir=ltr align=left><SPAN class=869074416-03092009><FONT color=#0000ff
size=3></FONT></SPAN> </P>
<P dir=ltr align=left><SPAN class=869074416-03092009><FONT color=#0000ff
size=3>Unless, you happen to be one of those poor mortals doomed to spend your
days toiling on something other than Unix .....</FONT></SPAN></P>
<P dir=ltr align=left><SPAN class=869074416-03092009><FONT color=#0000ff
size=3></FONT></SPAN> </P>
<P dir=ltr align=left><SPAN class=869074416-03092009></SPAN> </P><SPAN
class=869074416-03092009>
<P><FONT color=#0000ff size=3>Guy L. Werry<BR>Senior Systems Analyst<BR>Hudson
Bay Mining & Smelting Co., Limited.</FONT> </P></SPAN></SPAN></FONT>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
</DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma><B>From:</B> Peter Bateman [mailto:peterbateman808@hotmail.com]
<BR><B>Sent:</B> Thursday, September 03, 2009 11:35 AM<BR><B>To:</B> Guy Werry;
PowerHouse List<BR><B>Subject:</B> RE: Formatting a number in PowerHouse -
version 8.49.E<BR></FONT><BR></DIV>
<DIV></DIV>Hi all;<BR> <BR> Guy makes an interesting
point.<BR> <BR> I think the following sed command would get rid
of the comma<BR> in INNUM. Note the ` to cause the output from sed
to be put in the variable.<BR> It has been awhile since I have
coded UNIX scripts. Guy if you have access to <BR> an UNIX box
please try it.<BR><BR>> def INNUM char*20 = "1,234,567.89"<BR>> <BR>>
def OUTNUM num = nconvert(lj(getsystemval("OUTVAR"))) * 1000 &<BR>> If
setsystemval("OUTVAR",( "`<FONT face="Courier New">sed 's/,//g' < echo "
+</FONT><FONT face="Courier New"> INMUM ) </FONT>)<BR> <BR>
<BR>> Subject: RE: Formatting a number in PowerHouse - version 8.49.E<BR>>
Date: Wed, 2 Sep 2009 08:04:26 -0500<BR>> From: guy.werry@hbms.ca<BR>> To:
powerh-l@lists.sowder.com<BR>> <BR>> The original post didn't specify what
system they're running on. What<BR>> about tools like Unix's awk or perhaps
even Perl?<BR>> I haven't attempted this at all, but I have used awk pretty
often in the<BR>> past for similar situations. It may well be smart enough to
figure this<BR>> field out for you.<BR>> <BR>> Guy L. Werry<BR>>
Senior Systems Analyst<BR>> Hudson Bay Mining & Smelting Co., Limited.
<BR>> <BR>> -----Original Message-----<BR>> From:
powerh-l-bounces+guy.werry=hbms.ca@lists.sowder.com<BR>>
[mailto:powerh-l-bounces+guy.werry=hbms.ca@lists.sowder.com] On Behalf<BR>>
Of Knox, Dave (Carrollton, TX)<BR>> Sent: Tuesday, September 01, 2009 12:41
PM<BR>> To: powerh-l@lists.sowder.com<BR>> Subject: RE:Formatting a number
in PowerHouse - version 8.49.E<BR>> <BR>> The almost fool proof solution
(for HP3000 MPE at least).<BR>> <BR>> Just need to remove the commas.
Using getsystemval/setsystemval and an<BR>> MPE FUNCTION you can strip them
out in one statement. No formatting<BR>> control required, and all done in
one statement. Any number of decimals<BR>> (or none).<BR>> <BR>> def
INNUM char*20 = "1,234,567.89"<BR>> <BR>> def OUTNUM num =
nconvert(lj(getsystemval("OUTVAR"))) * 1000 &<BR>> If
setsystemval("OUTVAR",'![repl("' + trunc(INNUM) + '",",","")]')<BR>> <BR>>
;; the setsystemval is evaluated before the getsystemval is actioned.<BR>>
<BR>> <BR>> rep INNUM OUTNUM pic "^^^^^^^^^.^^^"<BR>> go<BR>>
<BR>> INNUM OUTNUM<BR>> <BR>> 1,234,567.89 1234567.890<BR>> <BR>>
<BR>> Caveat<BR>> Matchpattern required if input data not
trustworthy.<BR>> TRAILING negatives can be a problem (flip to front if
necessary)<BR>> No idea if this works on other platforms!<BR>> <BR>>
Regards<BR>> Dave Knox<BR>> <BR>>
----------------------------------------------------------------------<BR>>
<BR>> Message: 1<BR>> Date: Tue, 1 Sep 2009 12:56:34 -0400<BR>> From:
"Ken Langendock" <ken.langendock@rogers.com><BR>> Subject: RE:
Formatting a number in PowerHouse - version 8.49.E<BR>> To:
<powerh-l@lists.sowder.com><BR>> Message-ID:
<001701ca2b25$2a577740$7f0665c0$@langendock@rogers.com><BR>>
Content-Type: text/plain; charset="us-ascii"<BR>> <BR>> There is no fool
proof solution unless the string is exactly the same<BR>> every time.and from
what he said here, this is not the case.<BR>> <BR>> By using INDEX, this
solution is relatively easy:<BR>> <BR>> <BR>> <BR>> TEMP T-XAmount
CHARACTER * 20<BR>> <BR>> ITEM T-XAmount = Field OF InputFile<BR>>
<BR>> ; remove 1st ','<BR>> <BR>> ITEM T-XAmount = T-XAmount
[INDEX(T-XAmount,',') + 1:20] &<BR>> <BR>> IF 0 NE
INDEX(T-XAmount,',') <BR>> <BR>> ; remove 2nd ','<BR>> <BR>> ITEM
T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &<BR>> <BR>> IF 0
NE INDEX(T-XAmount,',') <BR>> <BR>> ; remove 3rd ','<BR>> <BR>> ITEM
T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &<BR>> <BR>> IF 0
NE INDEX(T-XAmount,',') <BR>> <BR>> . etc.<BR>> <BR>> ITEM T-Amount
= NCONVERT(T-XAmount)<BR>> <BR>> <BR>> <BR>> Ken<BR>> <BR>>
<BR>> <BR>> From:
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com<BR>>
[mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com] On<BR>>
Behalf Of Lowe, Chuck J<BR>> Sent: August 31, 2009 1:04 PM<BR>> To:
powerh-l@lists.sowder.com<BR>> Subject: [Bulk] Formatting a number in
PowerHouse - version 8.49.E<BR>> <BR>> <BR>> <BR>> I have a file
from an outside vendor. They are sending it pipe<BR>> delimited "|", There is
an amount field that is left justified. Is there<BR>> anyway to take that
character field and convert it to a numeric field<BR>> without doing<BR>>
INDEX and bit extracts. Sometimes the field is 123.45 and sometimes<BR>>
3,123.45. I thought I could use the FORMATNUMBER function but that is<BR>>
for doing the opposite. <BR>> <BR>> <BR>> <BR>> The vendor states
they can not send it unformatted. <BR>> <BR>> <BR>> <BR>> Thanks in
advance. <BR>> <BR>> <BR>> <BR>> Chuck Lowe Quest Diagnostics | Sr.
Programmer/Analyst,SYS Billing<BR>> System/Denver| 400 Egypt Rd. | West
Norriton, PA 19403 USA | phone<BR>> 610-650-6679| fax 610-650-2111 |<BR>>
<mailto:Chuck.J.Lowe@questdiagnostics.com><BR>>
Chuck.J.Lowe@questdiagnostics.com<BR>> |
<http://www.questdiagnostics.com/> www.QuestDiagnostics.com<BR>>
<BR>> Please think about resource conservation before you print this
message<BR>> <BR>> <BR>> <BR>> <BR>>
------------------------------------------<BR>> The contents of this message,
together with any attachments, are<BR>> intended only for the use of the
person(s) to which they are addressed<BR>> and may contain confidential
and/or privileged information. Further, any<BR>> medical information herein
is confidential and protected by law. It is<BR>> unlawful for unauthorized
persons to use, review, copy, disclose, or<BR>> disseminate confidential
medical information. If you are not the<BR>> intended recipient, immediately
advise the sender and delete this<BR>> message and any attachments.<BR>>
Any distribution, or copying of this message, or any attachment, is<BR>>
prohibited.<BR>> <BR>> -------------- next part --------------<BR>> An
HTML attachment was scrubbed...<BR>> URL:<BR>>
http://lists.sowder.com/pipermail/powerh-l/attachments/20090901/de04668f<BR>>
/attachment.html <BR>> <BR>> ------------------------------<BR>>
<BR>> --<BR>> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list:<BR>> powerh-l@lists.sowder.com<BR>> Subscribe:
&quot;subscribe&quot; in message body to<BR>>
powerh-l-request@lists.sowder.com<BR>> Unsubscribe: &quot;unsubscribe
&lt;password&gt;&quot; in message body to<BR>>
powerh-l-request@lists.sowder.com<BR>>
http://lists.sowder.com/mailman/listinfo/powerh-l<BR>> This list is closed,
thus to post to the list you must be a subscriber.<BR>> Add
'site:lists.sowder.com powerh-l' to your search terms to search the<BR>> list
archive at Google.<BR>> <BR>> End of powerh-l Digest, Vol 52, Issue
2<BR>> ***************************************<BR>> <BR>> <BR>>
--<BR>> = = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing
list:<BR>> powerh-l@lists.sowder.com<BR>> Subscribe: 'subscribe' in
message body to<BR>> powerh-l-request@lists.sowder.com<BR>> Unsubscribe:
'unsubscribe &lt;password&gt;' in message body to<BR>>
powerh-l-request@lists.sowder.com<BR>>
http://lists.sowder.com/mailman/listinfo/powerh-l<BR>> This list is closed,
thus to post to the list you must be a subscriber.<BR>> Add
'site:lists.sowder.com powerh-l' to your search terms to search the<BR>> list
archive at Google.<BR>> <BR>> The information in this e-mail and any
attachments is confidential and may be subject to legal professional privilege.
It is intended solely for the attention and use of the named addressee(s). If
you are not the intended recipient, or person responsible for delivering this
information to the intended recipient, please notify the sender immediately.
Unless you are the intended recipient or his/her representative you are not
authorised to, and must not, read, copy, distribute, use or retain this message
or any part of it.<BR>> <BR>> -- <BR>> = = = = = = = = = = = = = = = =
= = = = = = = = = = = =<BR>> Mailing list: powerh-l@lists.sowder.com<BR>>
Subscribe: 'subscribe' in message body to
powerh-l-request@lists.sowder.com<BR>> Unsubscribe: 'unsubscribe
&lt;password&gt;' in message body to
powerh-l-request@lists.sowder.com<BR>>
http://lists.sowder.com/mailman/listinfo/powerh-l<BR>> This list is closed,
thus to post to the list you must be a subscriber.<BR>> Add
'site:lists.sowder.com powerh-l' to your search terms to search the list archive
at Google.<BR><BR>
<HR>
Faster Hotmail access now on the new <A
href="http://go.microsoft.com/?linkid=9677399" target=_new>MSN homepage.</A>
The information in this e-mail and any attachments is confidential and may be subject to legal professional privilege. It is intended solely for the attention and use of the named addressee(s). If you are not the intended recipient, or person responsible for delivering this information to the intended recipient, please notify the sender immediately. Unless you are the intended recipient or his/her representative you are not authorised to, and must not, read, copy, distribute, use or retain this message or any part of it.
</BODY></HTML>