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



Desired Output is the following 

Desired_XSL_Out.txt




XML* XSLT

Avatar of undefined
Last Comment
kalyangkm

8/22/2022 - Mon
ASKER
kalyangkm

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.
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

Gertone (Geert Bormans)

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
kalyangkm

Hi Geert,

How should I incorporate this into my code as I have template match for the entire "Detail" record.
ASKER
kalyangkm

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
Gertone (Geert Bormans)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
kalyangkm

Thanks Geert.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.