Link to home
Start Free TrialLog in
Avatar of kalyangkm
kalyangkmFlag for United States of America

asked on

Format number removing the decimal upto to 2 and Right Align the string

Hi,


I need to achieve the following number formatting as well as Right alignment of the string. 

The number needs to be formatted based on the oracle format  999999V99 where the 2 decimals need to be taken into account when rounding the number and total length after formatting shouldn't exceed 8 characters.

For example  

If the Number is 2.12. It should be formatted as 212

If it is 2.143 it should be formatted to 214

If it is 345679.1 it should be formatted as 3456791`0



Here is my XML and the XSLT 

The area of interest is only the Deduction Amount part which needs to be right aligned and formatted as above


XML 

<?xml version="1.0" encoding="UTF-8"?><Records>
  <Details>     <Record_Type>D</Record_Type>     <Employee_FEI>123450767</Employee_FEI>     <Alternate_Employee_ID>00000006</Alternate_Employee_ID>     <Last_Name>Test</Last_Name>     <First_Name>Pawan</First_Name>     <Payroll_Status>A</Payroll_Status>     <Pay_Frequency>Z</Pay_Frequency>     <Deduction_Code>MP</Deduction_Code>     <Deduction_Amount>0</Deduction_Amount>     <Check_Date>20210601</Check_Date>     <Pay_Period_End_Date>20210601</Pay_Period_End_Date>     <Deduction_Year_Month>20210601</Deduction_Year_Month>     <Division>100</Division>     <Line_of_Business>100</Line_of_Business>     <Department_Cost_Center>100</Department_Cost_Center>     <Payroll_Company_Entity>100</Payroll_Company_Entity>     <Pay_Group>Group100</Pay_Group>     <Group_Number/>     <Location_Number/>     <Schedule_ID/>     <Filler/>     <Pay_Begin>09/04/2021</Pay_Begin>     <Pay_End>09/17/2021</Pay_End>   </Details>   <Details>     <Record_Type>D</Record_Type>     <Employee_FEI>345666666</Employee_FEI>     <Alternate_Employee_ID>01047276</Alternate_Employee_ID>     <Last_Name>Test</Last_Name>     <First_Name>Jacky</First_Name>     <Payroll_Status>A</Payroll_Status>     <Pay_Frequency>W</Pay_Frequency>     <Deduction_Code>EI</Deduction_Code>     <Deduction_Amount>345678.1</Deduction_Amount>     <Check_Date>20210502</Check_Date>     <Pay_Period_End_Date>20210502</Pay_Period_End_Date>     <Deduction_Year_Month>20210502</Deduction_Year_Month>     <Division>100005</Division>     <Line_of_Business>200032</Line_of_Business>     <Department_Cost_Center>401332</Department_Cost_Center>     <Payroll_Company_Entity>1020</Payroll_Company_Entity>     <Pay_Group>09</Pay_Group>     <Group_Number/>     <Location_Number/>     <Schedule_ID/>     <Filler/>     <Pay_Begin>09/12/2021</Pay_Begin>     <Pay_End>09/18/2021</Pay_End>   </Details>   <Details>     <Record_Type>D</Record_Type>     <Employee_SSN>001049696</Employee_SSN>     <Alternate_Employee_ID>01049696</Alternate_Employee_ID>     <Last_Name>Test</Last_Name>     <First_Name>Chressy</First_Name>     <Payroll_Status>A</Payroll_Status>     <Pay_Frequency>W</Pay_Frequency>     <Deduction_Code>EI</Deduction_Code>     <Deduction_Amount>2.123</Deduction_Amount>     <Check_Date>20210426</Check_Date>     <Pay_Period_End_Date>20210426</Pay_Period_End_Date>     <Deduction_Year_Month>20210426</Deduction_Year_Month>     <Division>100005</Division>     <Line_of_Business>200039</Line_of_Business>     <Department_Cost_Center>401462</Department_Cost_Center>     <Payroll_Company_Entity>1020</Payroll_Company_Entity>     <Pay_Group>09</Pay_Group>     <Group_Number/>     <Location_Number/>     <Schedule_ID/>     <Filler/>     <Pay_Begin>09/12/2021</Pay_Begin>     <Pay_End>09/18/2021</Pay_End>   </Details> </Records>

Open in new window




XSL


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0">     <xsl:param name="Detail_Record_Type"/>     <xsl:param name="Detail_Group_Number"/>     <xsl:param name="Detail_Location_Number"/>     <xsl:param name="Detail_Schedule_ID"/>           <xsl:variable name="spaces" select="'                                                                                                                                                                       '"/>          <xsl:template match="Details">         <xsl:value-of select="substring(concat('0145', $spaces), 1, 4)"/> <!-- the fixed 0145 value -->         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 5 spaces in between for UNION -->         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat(Employee_SSN, $spaces), 1, 13)"/>         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat(Last_Name, $spaces), 1, 25)"/>         <xsl:value-of select="substring(concat(First_Name, $spaces), 1, 25)"/>         <xsl:value-of select="substring(concat('', $spaces), 1, 20)"/><!-- the 20 spaces in between for Middle Name -->         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat('', $spaces), 1, 13)"/><!-- the 13 spaces in between for FSOID -->         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat(Deduction_Code, $spaces), 1, 2)"/>         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat('W', $spaces), 1, 1)"/> <!-- the fixed W value-->         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <!--<xsl:value-of select="substring(concat(format-number(Deduction_Amount,'#.00'), $spaces), 1, 9)"/>-->         <td style="text-align: right;"><xsl:value-of select="substring(concat(format-number(Deduction_Amount,'########'), $spaces), 1, 9)"/></td>        <!-- <xsl:value-of select="substring(concat(Deduction_Amount, $spaces), format-number(.,'#0.00'), string-length(format-number(.,'#0.00')), 1, 9)"/> -->        <!-- <xsl:value-of select="substring(concat(Deduction_Amount, $spaces), 1, 9)"/> -->         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat(Pay_Begin, $spaces), 1, 10)"/>         <xsl:value-of select="substring(concat('', $spaces), 1, 1)"/><!-- the 1 spaces in between for Filler -->         <xsl:value-of select="substring(concat(Pay_End, $spaces), 1, 10)"/>                  <!-- more to come -->         <xsl:text>&#10;</xsl:text>     </xsl:template>     <xsl:output omit-xml-declaration="no" indent="yes" method="text"/>    <xsl:strip-space elements="*"/>     <xsl:template match="node()|@*">       <xsl:copy>          <xsl:apply-templates select="node()|@*"/>       </xsl:copy>    </xsl:template> </xsl:stylesheet>

Open in new window



Here is the output of above

XSL Output.txt


User generated image


Desired Output is the following 

Desired_XSL_Out.txt


User generated image

Avatar of kalyangkm
kalyangkm
Flag of United States of America image

ASKER

Hi

I am just able to get the number formatting right using the following code
<td style="text-align: right;"><xsl:value-of select="substring(concat(format-number(Deduction_Amount*100,'########'), $spaces), 1, 9)"/></td>

But I still have issue with RIght align.
Avatar of Gertone (Geert Bormans)
<tests>
    <test>2.12</test>
    <test>2.143</test>
    <test>345679.1</test>
    <test>1345679.1</test>
</tests>

Open in new window

I added one to the test... what happens if there are too many digits... my solution breaks then

XSLT3
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"
     expand-text="yes"
    version="3.0">
    
    <xsl:mode on-no-match="shallow-copy"/>
    
    <xsl:strip-space elements="*"/>
    <xsl:output indent="yes"/>
    
    <xsl:template match="test">
        <xsl:variable name="str" select="format-number(. * 100, '#')"/>
        <test>
            <xsl:value-of select="for $i in 1 to (8 - string-length($str)) return ' '" separator=""/>
            <xsl:value-of select="$str"/>        
        </test>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window

The heart is in the test template
construct the number string in a variable
in a for construct add the correct amount of spaces
(note that you generate a sequence of items this way and a simple value-of would add an extra whitespace... hence the seperator = empty string

An XSLT1 solution would be
- construct the string
- add 8 spaces in front of that
- take the last 8 characters from the concatenate string using substring
    <xsl:template match="test">
        <xsl:variable name="str" select="concat('        ', format-number(. * 100, '#'))"/>
        <test>
            <xsl:value-of select="substring($str, string-length($str) - 7)"/>        
        </test>
    </xsl:template>


Open in new window

Hi Geert,

How should I incorporate this into my code as I have template match for the entire "Detail" record.
Also I suppose your piece code also aligns it to the right? Please let me know how to incorporate your piece of code into my XSLT above
ASKER CERTIFIED SOLUTION
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Geert.