<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>By using INDEX, this solution is relatively easy:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> TEMP T-XAmount CHARACTER * 20<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> ITEM T-XAmount = Field OF InputFile<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>; remove 1st ','<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> ITEM T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> IF 0 NE
INDEX(T-XAmount,',') <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>; remove 2nd ','<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> ITEM T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> IF 0 NE
INDEX(T-XAmount,',') <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>; remove 3rd ','<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> ITEM T-XAmount = T-XAmount [INDEX(T-XAmount,',') + 1:20] &<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> IF 0 NE
INDEX(T-XAmount,',') <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>… etc.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>ITEM T-Amount = NCONVERT(T-XAmount)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Ken<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com] <b>On
Behalf Of </b>Lowe, Chuck J<br>
<b>Sent:</b> August 31, 2009 1:04 PM<br>
<b>To:</b> powerh-l@lists.sowder.com<br>
<b>Subject:</b> [Bulk] Formatting a number in PowerHouse - version 8.49.E<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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. </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>The
vendor states they can not send it unformatted. </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Thanks
in advance. </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<p><b><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#333333'>Chuck Lowe </span></b><b><span style='font-size:8.5pt;
font-family:"Verdana","sans-serif";color:#006633'>Quest Diagnostics</span></b><span
style='font-family:"Verdana","sans-serif"'> </span><span style='font-size:8.5pt;
font-family:"Verdana","sans-serif";color:#666666'>| Sr. Programmer/Analyst,SYS
Billing System/Denver| 400 Egypt Rd. | West Norriton, PA 19403 USA | <strong><span
style='font-family:"Verdana","sans-serif"'>phone</span></strong> 610-650-6679| <strong><span
style='font-family:"Verdana","sans-serif"'>fax</span></strong> 610-650-2111
| <a href="mailto:Chuck.J.Lowe@questdiagnostics.com"><span
style='color:#666666'>Chuck.J.Lowe@questdiagnostics.com</span></a> | <a
href="http://www.questdiagnostics.com/"><span style='color:#666666'>www.QuestDiagnostics.com</span></a>
</span><span style='font-family:"Verdana","sans-serif"'><o:p></o:p></span></p>
<p style='margin-bottom:48.0pt'><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";
color:#006633'>Please think about resource conservation before you print this
message<o:p></o:p></span></p>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<p class=MsoNormal><br>
------------------------------------------ <br>
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.<o:p></o:p></p>
</div>
</body>
</html>