<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
</style>
</head>
<body class='hmmessage'>
<meta http-equiv="Content-Type" content="text/html; charset=unicode">
<meta name="Generator" content="Microsoft SafeHTML">
<style>
.hmmessage P
{margin:0px;padding:0px;}
body.hmmessage
{font-size:10pt;font-family:Verdana;}
</style>
Hi Guy:<br><br> I glad you have your awk code working. <br><br> The object of the test on UNIX is to see if we can do the same as what Dave Knox did on MPE/iX.<br><br> That is to assign a system variable the result of a call to PowerHouse's setsystemval whose second parameter is a valid<br> script expression. <br><br> The ignoring of separators is something that NCONVERT needs.<br><br> There well may be more overhead in running out to the OS everytime we want to NCONVERT a string.<br> Transforming the input file maybe a better choice.<br><br>Regards,<br>Peter<br><br><hr id="stopSpelling">Subject: RE: Formatting a number in PowerHouse - version 8.49.E<br>Date: Thu, 3 Sep 2009 13:11:07 -0500<br>From: guy.werry@hbms.ca<br>To: powerh-l@lists.sowder.com<br><br>
<style>
.ExternalClass .EC_hmmessage P
{padding-right:0px;padding-left:0px;padding-bottom:0px;padding-top:0px;}
.ExternalClass BODY.EC_hmmessage
{font-size:10pt;font-family:Verdana;}
</style>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009">Peter, and all the rest of you Wise Powerhousers out
there ....</span></font></div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009"></span></font> </div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_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 size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009"></span></font> </div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_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 size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009"> cat g | sed
's/,//g'</span></font></div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009"></span></font> </div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009">This converts the number
wonderfully.</span></font></div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009"></span></font> </div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_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 size="3" color="#0000ff" face="Arial"><span class="EC_869074416-03092009"></span></font> </div>
<div dir="ltr" align="left"><font size="3" color="#0000ff" face="Arial"><span class="EC_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 size="3" color="#0000ff" face="Arial"><span class="EC_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="EC_869074416-03092009">
<p dir="ltr" align="left"><font color="#0000ff" face="r_ansi"></font> </p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">cat g.awk</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">BEGIN { FS = "|"; OFS="|"
}</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">{</font></p>
<p dir="ltr" align="left"><font color="#0000ff"><font size="3"><span class="EC_869074416-03092009"> </span>MFGP = ""</font></font></p>
<p dir="ltr" align="left"><font color="#0000ff"><font size="3"><span class="EC_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="EC_869074416-03092009">
</span>{</font></font></p>
<p dir="ltr" align="left"><font color="#0000ff"><font size="3"><span class="EC_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="EC_869074416-03092009">
</span>MFGP = MFGP toupper(substr($2,i,1))</font></font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">}</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">print $1, $2, $3, $4, "
Re-formatted field 2 ", MFGP</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">}</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff"></font> </p>
<p dir="ltr" align="left"><font size="3" color="#0000ff"></font> </p>
<p dir="ltr" align="left"><font color="#0000ff"><font size="3"><span class="EC_869074416-03092009">Here is our input file:</span> cat
/tmp/glw/g1</font></font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff">a text field, with commas,
needs them|1,234,569.89 |1234|test</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff"></font> </p>
<p dir="ltr" align="left"><span class="EC_869074416-03092009"><font size="3" color="#0000ff">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 size="3" color="#0000ff">cat /tmp/glw/g1|awk -f
g.awk</font></p>
<p dir="ltr" align="left"><font size="3" color="#0000ff"></font> </p>
<p dir="ltr" align="left"><span class="EC_869074416-03092009"><font size="3" color="#0000ff">Gives the following result:</font></span></p>
<blockquote dir="ltr" style="margin-right: 0px;">
<font size="3" color="#0000ff">a text field, with commas, needs
them|1,234,569.89 |1234|test|Re-formatted field 2 |1234569.89</font><br>
<font size="3" color="#0000ff"></font> <br></blockquote>
<p dir="ltr" align="left"><span class="EC_869074416-03092009"><font size="3" color="#0000ff">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="EC_869074416-03092009"><font size="3" color="#0000ff"></font></span> </p>
<p dir="ltr" align="left"><span class="EC_869074416-03092009"><font size="3" color="#0000ff">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="EC_869074416-03092009"><font size="3" color="#0000ff"></font></span> </p>
<p dir="ltr" align="left"><span class="EC_869074416-03092009"></span> </p><span class="EC_869074416-03092009">
<font size="3" color="#0000ff">Guy L. Werry<br>Senior Systems Analyst<br>Hudson
Bay Mining & Smelting Co., Limited.</font> <br></span></span></font>
<div class="EC_OutlookMessageHeader" dir="ltr" align="left" lang="en-us">
<hr>
</div>
<div class="EC_OutlookMessageHeader" dir="ltr" align="left" lang="en-us"><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<b>,567.89"<br>> <br></b>>
def OUTNUM num = nconvert(lj(getsystemval("OUTVAR"))) * 1000 &<br>> If
setsystemval("OUTVAR",( "<font style="" face="Courier New">`sed 's/,//g' < echo "
+</font><font style="" face="Courier New"> INMUM ) </font>)<b><br> </b><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">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.<br /><hr />New! Open Hotmail faster on the new <a href='http://go.microsoft.com/?linkid=9677400' target='_new'>MSN homepage!</a></body>
</html>