CODEWITHSUNDEEP
xsltxslt-1.0xslt-2.0
S4NDM4N
S4NDM4N

Reputation: 924

Sum function not working when selecting value after matching condition

I'm trying to extract and sum values from a XML file. The data set have an element called "Pallets" which contains "Y" or "N". So if the "Pallets" value equals "Y" then I want to get the value in "Amount". I went through lot of XSL codes here and on the internet then I came up with the below code part.

<xsl:value-of select=
    "('&#xD;Pallet Amount',
      $fullPath/LineItemRows/LineItemRow/Fields
              [Field[@Label = 'Pallets']/Value = 'Y']
                     /sum(number(translate(Field[@Label = 'Amount']/Value,
                                           ',',
                                           '.')
                                 )
                          )
      )"/>

Which is producing the below out put,

Pallet Amount 7.21 7.21 14.06

As you guys can see I do get the values but they're not summing up. My desired out put is 28.48.

Below is a sample set of data which is similar to the XML I'm using (the actual file is too big and it generated by a software we use).

<LineItemRows>
        <LineItemRow ID="0" FromPage="1">
          <Fields>                
            <Field ID="a16255ad1e9549b48a9a31d001629b36" Type="Text" Status="Complete" Label="Pallets">
              <Value>N</Value>
            </Field>
            </Field>
            <Field ID="252a9390778540edac6520d3f9f260b8" Type="Text" Status="Complete" Label="Amount">
              <Value>406,74</Value>
            </Field>
          </Fields>
        </LineItemRow>
        <LineItemRow ID="3" FromPage="1">
          <Fields>
            <Field ID="a16255ad1e9549b48a9a31d001629b36" Type="Text" Status="Complete" Label="Pallets">
              <Value>Y</Value>
            </Field>                
            <Field ID="252a9390778540edac6520d3f9f260b8" Type="Text" Status="Complete" Label="Amount">
              <Value>7,21</Value>
            </Field>
          </Fields>
        </LineItemRow>
        <LineItemRow ID="0" FromPage="1">
            <Field ID="a16255ad1e9549b48a9a31d001629b36" Type="Text" Status="Complete" Label="Pallets">
              <Value>N</Value>
            </Field>
            <Field ID="252a9390778540edac6520d3f9f260b8" Type="Text" Status="Complete" Label="Amount">
              <Value>33,92</Value>
            </Field>
          </Fields>
        </LineItemRow>
        <LineItemRow ID="5" FromPage="1">
            <Field ID="a16255ad1e9549b48a9a31d001629b36" Type="Text" Status="Complete" Label="Pallets">
              <Value>Y</Value>
            </Field>                
            <Field ID="252a9390778540edac6520d3f9f260b8" Type="Text" Status="Complete" Label="Amount">
              <Value>7,21</Value>
            </Field>
          </Fields>
        </LineItemRow>
        <LineItemRow ID="6" FromPage="1">
            <Field ID="a16255ad1e9549b48a9a31d001629b36" Type="Text" Status="Complete" Label="Pallets">
              <Value>Y</Value>
            <Field ID="252a9390778540edac6520d3f9f260b8" Type="Text" Status="Complete" Label="Amount">
              <Value>14,06</Value>
            </Field>
          </Fields>
        </LineItemRow>
    </LinItemRows>

I also tried some other ways but those literally didn't work. Hope I made my question clear. Also I'm using xsl version 2.0 but system is compatible with version 1.0 as well.

Upvotes: 0

Views: 54

Answers (1)

Dubh
Dubh

Reputation: 101

The XPath 2 expression

sum(LineItemRows/LineItemRow/Fields[Field[@Label='Pallets' and Value='Y']]/Field[@Label='Amount']/xs:decimal(translate(Value,',','.')))

If there is any risk that the Y or N value could be padded with whitespace then use normalize-space(Value)='Y' for that value test.

Your original expression was applying the summing within the LineItemRow which would only have found a single value and so you were outputting a sequence of those single-value sums.

The expression here also performs the sum using the XMLSchema Decimal type (declare the namespace xmlns:xs="http://www.w3.org/2001/XMLSchema" for the prefix used in that expression) rather than the default double precision number representation which should avoid inaccuracies and the need to round the sums to two digits.

Upvotes: 1

Related Questions