PManual
Reputation: 15
XSLT Sum (multiple conditions)
I used the answer at XSLT : Cummulative Sum (Conditional) as 'base' to do the sum on a different xml and I'm not getting the correct sum.
The output should be created for only the 'skus' within Root/Items/Result/Sku. The cummulative sum should be grouped by 'Provider Quality' (main, damaged) for the only the providers within Root/Providers/Result/
I'm sure that my predicate condition within the sum is wrong. But I'm not able to figure out the issue. I would appreciate any help. Thanks!
Sample XML:
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Items>
<Result>
<Sku>XYZ</Sku>
</Result>
<Result>
<Sku>ABC</Sku>
</Result>
</Items>
<Providers>
<Result>
<ProviderCode>1_M</ProviderCode>
<Quality>Main</Quality>
</Result>
<Result>
<ProviderCode>1_D</ProviderCode>
<Quality>Damaged</Quality>
</Result>
<Result>
<ProviderCode>2_M</ProviderCode>
<Quality>Main</Quality>
</Result>
<Result>
<ProviderCode>2_D</ProviderCode>
<Quality>Damaged</Quality>
</Result>
</Providers>
<Message>
<Body>
<Inventory>
<SKU>
<SKU>AXYZ</SKU>
<Description>XYZ Description</Description>
<Providers>
<Provider>
<ProviderCode>1_M</ProviderCode>
<Qty>100</Qty>
</Provider>
<Provider>
<ProviderCode>2_M</ProviderCode>
<Qty>67</Qty>
</Provider>
<Provider>
<ProviderCode>2_D</ProviderCode>
<Qty>75</Qty>
</Provider>
<Provider>
<ProviderCode>3_M</ProviderCode>
<Qty>74</Qty>
</Provider>
<Provider>
<ProviderCode>4</ProviderCode>
<Qty>62</Qty>
</Provider>
</Providers>
</SKU>
<SKU>
<SKU>ABC</SKU>
<Description>ABC Description</Description>
<Providers>
<Provider>
<ProviderCode>1_M</ProviderCode>
<Qty>20</Qty>
</Provider>
<Provider>
<ProviderCode>1_D</ProviderCode>
<Qty>205</Qty>
</Provider>
<Provider>
<ProviderCode>2_M</ProviderCode>
<Qty>77</Qty>
</Provider>
<Provider>
<ProviderCode>2_D</ProviderCode>
<Qty>5</Qty>
</Provider>
<Provider>
<ProviderCode>3_M</ProviderCode>
<Qty>42</Qty>
</Provider>
<Provider>
<ProviderCode>4_M</ProviderCode>
<Qty>631</Qty>
</Provider>
</Providers>
</SKU>
</Inventory>
</Body>
</Message>
</Root>
XSLT used:
<xsl:output method="xml" indent="yes"/>
<xsl:key name="validProviders" match="/Root/Providers/Result" use="ProviderCode"/>
<xsl:key name="validProviderQuality" match="/Root/Providers/Result" use="Quality"/>
<xsl:key name="inboundSkus" match="/Root/Message/Body/Inventory/SKU" use="SKU"/>
<xsl:template match="/">
<Root>
<xsl:apply-templates select="/Root/Items/Result"/>
</Root>
</xsl:template>
<xsl:template match="/Root/Items/Result">
<xsl:if test="key('inboundSkus',Sku)">
<SKU>
<xsl:variable name="Sku" select="Sku"/>
<xsl:for-each select="/Root/Providers/Result[generate-id(.)=generate-id(key('validProviderQuality',Quality)[1])]">
<Record>
<xsl:variable name="ProviderQuality" select="Quality" />
<xsl:element name="Sku"><xsl:value-of select="$Sku"/></xsl:element>
<xsl:element name="Quality"><xsl:value-of select="$ProviderQuality"/></xsl:element>
<xsl:element name="Qty1"><xsl:value-of select="sum(/Root/Message/Body/Inventory/SKU[SKU=$Sku]/Providers/Provider[ProviderCode=/Root/Providers/Result[Quality=$ProviderQuality]]/Qty)"/></xsl:element>
<xsl:element name="Qty2"><xsl:value-of select="sum(/Root/Message/Body/Inventory/SKU[SKU=$Sku]/Providers/Provider[key('validProviderQuality',$ProviderQuality)]/Qty)"/></xsl:element>
</Record>
</xsl:for-each>
</SKU>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
Output:
<?xml version="1.0" encoding="utf-8"?>
<Root>
<SKU>
<Record>
<Sku>ABC</Sku>
<Quality>Main</Quality>
<Qty1>0</Qty1>
<Qty2>980</Qty2>
</Record>
<Record>
<Sku>ABC</Sku>
<Quality>Damaged</Quality>
<Qty1>0</Qty1>
<Qty2>980</Qty2>
</Record>
</SKU>
</Root>
Wanted result is
<Root>
<SKU>
<Record>
<Sku>ABC</Sku>
<Quality>Main</Quality>
<Qty>97</Qty>
</Record>
<Record>
<Sku>ABC</Sku>
<Quality>Damaged</Quality>
<Qty>210</Qty>
</Record>
</SKU>
</Root>
Upvotes: 0
Views: 5467
Answers (2)
hr_117
Reputation: 9627
Here a adaption of your stylesheet as xslt-1.0 solution. You may try this:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:key name="validProviderQuality" match="/Root/Providers/Result" use="Quality"/>
<xsl:variable name="mySkus" select="//Result/Sku" />
<xsl:template match="/">
<Root>
<xsl:apply-templates
select="/Root/Message/Body/Inventory/SKU[SKU = $mySkus]"/>
</Root>
</xsl:template>
<xsl:template match="/Root/Message/Body/Inventory/SKU" >
<xsl:variable name="Sku" select="SKU"/>
<xsl:for-each select="/Root/Providers/Result[generate-id(.)=
generate-id(key('validProviderQuality',Quality)[1])]">
<SKU>
<Record>
<xsl:variable name="ProviderQuality" select="Quality" />
<xsl:element name="Sku">
<xsl:value-of select="$Sku"/>
</xsl:element>
<xsl:element name="Quality">
<xsl:value-of select="$ProviderQuality"/>
</xsl:element>
<xsl:element name="Qty">
<xsl:value-of
select="sum(//SKU[SKU=$Sku]//Provider[
ProviderCode=/Root/Providers/Result[
Quality=$ProviderQuality
]/ProviderCode
]/Qty)"/>
</xsl:element>
</Record>
</SKU>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Which will generate the following output:
<?xml version="1.0"?>
<Root>
<SKU>
<Record>
<Sku>ABC</Sku>
<Quality>Main</Quality>
<Qty>97</Qty>
</Record>
</SKU>
<SKU>
<Record>
<Sku>ABC</Sku>
<Quality>Damaged</Quality>
<Qty>210</Qty>
</Record>
</SKU>
</Root>
Upvotes: 1
Martin Honnen
Reputation: 167401
I think the requirement is pretty complicated, to understand it better I have tried to implement it in XSLT 2.0. I don't have time to try to implement it in XSLT 1.0 however, so I will post the XSLT 2.0 code (you could run with an XSLT 2.0 processor like Saxon 9 or AltovaXML):
<xsl:stylesheet
version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xs">
<xsl:output indent="yes"/>
<xsl:variable name="wanted-skus" select="/Root/Items/Result/Sku"/>
<xsl:variable name="wanted-prvdrs" select="/Root/Providers/Result/ProviderCode"/>
<xsl:key name="qual" match="Root/Providers/Result" use="ProviderCode"/>
<xsl:template match="Root">
<xsl:copy>
<xsl:for-each-group
select="Message/Body/Inventory/SKU
[SKU = $wanted-skus]
/Providers/Provider[ProviderCode = $wanted-prvdrs]"
group-by="key('qual', ProviderCode)/Quality">
<Record>
<Sku><xsl:value-of select="ancestor::SKU/SKU"/></Sku>
<Quality><xsl:value-of select="current-grouping-key()"/></Quality>
<Qty><xsl:value-of select="sum(current-group()/Qty)"/></Qty>
</Record>
</xsl:for-each-group>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Using that stylesheet Saxon 9.5 transforms the input
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Items>
<Result>
<Sku>XYZ</Sku>
</Result>
<Result>
<Sku>ABC</Sku>
</Result>
</Items>
<Providers>
<Result>
<ProviderCode>1_M</ProviderCode>
<Quality>Main</Quality>
</Result>
<Result>
<ProviderCode>1_D</ProviderCode>
<Quality>Damaged</Quality>
</Result>
<Result>
<ProviderCode>2_M</ProviderCode>
<Quality>Main</Quality>
</Result>
<Result>
<ProviderCode>2_D</ProviderCode>
<Quality>Damaged</Quality>
</Result>
</Providers>
<Message>
<Body>
<Inventory>
<SKU>
<SKU>AXYZ</SKU>
<Description>XYZ Description</Description>
<Providers>
<Provider>
<ProviderCode>1_M</ProviderCode>
<Qty>100</Qty>
</Provider>
<Provider>
<ProviderCode>2_M</ProviderCode>
<Qty>67</Qty>
</Provider>
<Provider>
<ProviderCode>2_D</ProviderCode>
<Qty>75</Qty>
</Provider>
<Provider>
<ProviderCode>3_M</ProviderCode>
<Qty>74</Qty>
</Provider>
<Provider>
<ProviderCode>4</ProviderCode>
<Qty>62</Qty>
</Provider>
</Providers>
</SKU>
<SKU>
<SKU>ABC</SKU>
<Description>ABC Description</Description>
<Providers>
<Provider>
<ProviderCode>1_M</ProviderCode>
<Qty>20</Qty>
</Provider>
<Provider>
<ProviderCode>1_D</ProviderCode>
<Qty>205</Qty>
</Provider>
<Provider>
<ProviderCode>2_M</ProviderCode>
<Qty>77</Qty>
</Provider>
<Provider>
<ProviderCode>2_D</ProviderCode>
<Qty>5</Qty>
</Provider>
<Provider>
<ProviderCode>3_M</ProviderCode>
<Qty>42</Qty>
</Provider>
<Provider>
<ProviderCode>4_M</ProviderCode>
<Qty>631</Qty>
</Provider>
</Providers>
</SKU>
</Inventory>
</Body>
</Message>
</Root>
into the wanted result
<Root>
<Record>
<Sku>ABC</Sku>
<Quality>Main</Quality>
<Qty>97</Qty>
</Record>
<Record>
<Sku>ABC</Sku>
<Quality>Damaged</Quality>
<Qty>210</Qty>
</Record>
</Root>
Upvotes: 0
Related Questions
- XSLT: sum with condition
- How to sum values in a loop with conditions in Xslt
- XSL How to sum the node value based on condition
- How to do a sum from xml based on condition using xslt code
- XSL, SUM & Multiply with Condition
- Doing the sum of multiple value
- XSLT : Cummulative Sum (Conditional)
- How to sum up values with other specific element in XSLT
- xslt aggregation sum
- XSL conditional sum