Formatting a number in PowerHouse - version 8.49.E

Guy Werry guy.werry at hbms.ca
Thu Sep 3 13:11:07 CDT 2009 

Peter, and all the rest of you Wise Powerhousers out there ....
 
What Peter is suggesting will work, although my style would be as
follows (this works, I tested it):
 
    I put the value "1,234,567.89                                 "
(note the trailing spaces) into a file named "g"
    cat g | sed 's/,//g'
 
This converts the number wonderfully.
 
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.
 
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.
If there's an issue with other fields having commas that you don't want
to kill, then awk code like this will work:
 

cat g.awk

BEGIN { FS = "|"; OFS="|" }

{

    MFGP = ""

    for (i=1; i<= 20; ++i)

        {

        if ( substr($2,i,1) != "," && substr($2,i,1) != " " )

            MFGP = MFGP toupper(substr($2,i,1))

}

print $1, $2, $3, $4, " Re-formatted field 2 ", MFGP

}

 

 

Here is our input file: cat /tmp/glw/g1

a text field, with commas, needs them|1,234,569.89 |1234|test

 

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!

 

cat /tmp/glw/g1|awk -f g.awk

 

Gives the following result:

	a text field, with commas, needs them|1,234,569.89
|1234|test|Re-formatted field 2 |1234569.89

	 

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.

 

Unless, you happen to be one of those poor mortals doomed to spend your
days toiling on something other than Unix .....

 

 

Guy L. Werry
Senior Systems Analyst
Hudson Bay Mining & Smelting Co., Limited. 

________________________________

From: Peter Bateman [mailto:peterbateman808 at hotmail.com] 
Sent: Thursday, September 03, 2009 11:35 AM
To: Guy Werry; PowerHouse List
Subject: RE: Formatting a number in PowerHouse - version 8.49.E


Hi all;
 
   Guy makes an interesting point.
 
   I think the following sed command would get rid of the comma
   in INNUM. Note the ` to cause the output from sed to be put in the
variable.
   It has been awhile since I have coded UNIX scripts. Guy if you have
access to 
   an UNIX box please try it.

> def INNUM char*20 = "1,234,567.89"
> 
> def OUTNUM num = nconvert(lj(getsystemval("OUTVAR"))) * 1000 &
> If setsystemval("OUTVAR",( "`sed 's/,//g' < echo " + INMUM ) )
 
  
> Subject: RE: Formatting a number in PowerHouse - version 8.49.E
> Date: Wed, 2 Sep 2009 08:04:26 -0500
> From: guy.werry at hbms.ca
> To: powerh-l at lists.sowder.com
> 
> The original post didn't specify what system they're running on. What
> about tools like Unix's awk or perhaps even Perl?
> I haven't attempted this at all, but I have used awk pretty often in
the
> past for similar situations. It may well be smart enough to figure
this
> field out for you.
> 
> Guy L. Werry
> Senior Systems Analyst
> Hudson Bay Mining & Smelting Co., Limited. 
> 
> -----Original Message-----
> From: powerh-l-bounces+guy.werry=hbms.ca at lists.sowder.com
> [mailto:powerh-l-bounces+guy.werry=hbms.ca at lists.sowder.com] On Behalf
> Of Knox, Dave (Carrollton, TX)
> Sent: Tuesday, September 01, 2009 12:41 PM
> To: powerh-l at lists.sowder.com
> Subject: RE:Formatting a number in PowerHouse - version 8.49.E
> 
> The almost fool proof solution (for HP3000 MPE at least).
> 
> Just need to remove the commas. Using getsystemval/setsystemval and an
> MPE FUNCTION you can strip them out in one statement. No formatting
> control required, and all done in one statement. Any number of
decimals
> (or none).
> 
> def INNUM char*20 = "1,234,567.89"
> 
> def OUTNUM num = nconvert(lj(getsystemval("OUTVAR"))) * 1000 &
> If setsystemval("OUTVAR",'![repl("' + trunc(INNUM) + '",",","")]')
> 
> ;; the setsystemval is evaluated before the getsystemval is actioned.
> 
> 
> rep INNUM OUTNUM pic "^^^^^^^^^.^^^"
> go
> 
> INNUM OUTNUM
> 
> 1,234,567.89 1234567.890
> 
> 
> Caveat
> Matchpattern required if input data not trustworthy.
> TRAILING negatives can be a problem (flip to front if necessary)
> No idea if this works on other platforms!
> 
> Regards
> Dave Knox
> 
> ----------------------------------------------------------------------
> 
> Message: 1
> Date: Tue, 1 Sep 2009 12:56:34 -0400
> From: "Ken Langendock" <ken.langendock at rogers.com>
> Subject: RE: Formatting a number in PowerHouse - version 8.49.E
> To: <powerh-l at lists.sowder.com>
> Message-ID: <001701ca2b25$2a577740$7f0665c0$@langendock at rogers.com>
> Content-Type: text/plain; charset="us-ascii"
> 
> There is no fool proof solution unless the string is exactly the same
> every time.and from what he said here, this is not the case.
> 
> By using INDEX, this solution is relatively easy:
> 
> 
> 
> TEMP T-XAmount CHARACTER * 20
> 
> ITEM T-XAmount = Field OF InputFile
> 
> ; remove 1st ','
> 
> ITEM T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &
> 
> IF 0 NE INDEX(T-XAmount,',') 
> 
> ; remove 2nd ','
> 
> ITEM T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &
> 
> IF 0 NE INDEX(T-XAmount,',') 
> 
> ; remove 3rd ','
> 
> ITEM T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &
> 
> IF 0 NE INDEX(T-XAmount,',') 
> 
> . etc.
> 
> ITEM T-Amount = NCONVERT(T-XAmount)
> 
> 
> 
> Ken
> 
> 
> 
> From: powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com
> [mailto:powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com]
On
> Behalf Of Lowe, Chuck J
> Sent: August 31, 2009 1:04 PM
> To: powerh-l at lists.sowder.com
> Subject: [Bulk] Formatting a number in PowerHouse - version 8.49.E
> 
> 
> 
> I have a file from an outside vendor. They are sending it pipe
> delimited "|", There is an amount field that is left justified. Is
there
> anyway to take that character field and convert it to a numeric field
> without doing
> INDEX and bit extracts. Sometimes the field is 123.45 and sometimes
> 3,123.45. I thought I could use the FORMATNUMBER function but that is
> for doing the opposite. 
> 
> 
> 
> The vendor states they can not send it unformatted. 
> 
> 
> 
> Thanks in advance. 
> 
> 
> 
> Chuck Lowe Quest Diagnostics | Sr. Programmer/Analyst,SYS Billing
> System/Denver| 400 Egypt Rd. | West Norriton, PA 19403 USA | phone
> 610-650-6679| fax 610-650-2111 |
> <mailto:Chuck.J.Lowe at questdiagnostics.com>
> Chuck.J.Lowe at questdiagnostics.com
> | <http://www.questdiagnostics.com/> www.QuestDiagnostics.com
> 
> Please think about resource conservation before you print this message
> 
> 
> 
> 
> ------------------------------------------
> The contents of this message, together with any attachments, are
> intended only for the use of the person(s) to which they are addressed
> and may contain confidential and/or privileged information. Further,
any
> medical information herein is confidential and protected by law. It is
> unlawful for unauthorized persons to use, review, copy, disclose, or
> disseminate confidential medical information. If you are not the
> intended recipient, immediately advise the sender and delete this
> message and any attachments.
> Any distribution, or copying of this message, or any attachment, is
> prohibited.
> 
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
>
http://lists.sowder.com/pipermail/powerh-l/attachments/20090901/de04668f
> /attachment.html 
> 
> ------------------------------
> 
> --
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:
> powerh-l at lists.sowder.com
> Subscribe: &quot;subscribe&quot; in message body to
> powerh-l-request at lists.sowder.com
> Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body
to
> powerh-l-request at lists.sowder.com
> http://lists.sowder.com/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a
subscriber.
> Add 'site:lists.sowder.com powerh-l' to your search terms to search
the
> list archive at Google.
> 
> End of powerh-l Digest, Vol 52, Issue 2
> ***************************************
> 
> 
> --
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:
> powerh-l at lists.sowder.com
> Subscribe: 'subscribe' in message body to
> powerh-l-request at lists.sowder.com
> Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
> powerh-l-request at lists.sowder.com
> http://lists.sowder.com/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a
subscriber.
> Add 'site:lists.sowder.com powerh-l' to your search terms to search
the
> list archive at Google.
> 
> 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.
> 
> -- 
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Mailing list: powerh-l at lists.sowder.com
> Subscribe: 'subscribe' in message body to
powerh-l-request at lists.sowder.com
> Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to
powerh-l-request at lists.sowder.com
> http://lists.sowder.com/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a
subscriber.
> Add 'site:lists.sowder.com powerh-l' to your search terms to search
the list archive at Google.


________________________________

Faster Hotmail access now on the new MSN homepage.
<http://go.microsoft.com/?linkid=9677399>  

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20090903/81b7c30e/attachment-0001.htm

More information about the powerh-l mailing list