Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XSLT
Reload this Page Need to fetch distinct records on 2 field values
|
XSLT General questions and answers about XSLT. For issues strictly specific to the book XSLT 1.1 Programmers Reference, please post to that forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XSLT section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Page 1 of 2 1 2
 
Old March 17th, 2008, 01:12 AM
Authorized User
  
Join Date: Feb 2008
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need to fetch distinct records on 2 field values
Hi,

I have pasted the code at the end of the document.

 I have to retrieve distinct records based on the values to 2 fields:PrepackIndicator and UPC Code in the xml.

Here for the below XML there are 3 Line Items which has following lines of code.
  <VariableDataElement Code="750" Name="Prepack Indicator" Value="8060804465DXAAF" />
  <VariableDataElement Code="790" Name="UPC Code" Value="883241679910" />

The above line values are same for the first 2 LineItems where as the values are unique for the 3rd LineItem.

I need to get the distinct records based on these values.If there are duplicated values, only take the first record.

<LineItemSet Count="3">

+ <LineItem LineItemId="2241855" ItemId="001000020-883241501280" Quantity="60" OverrunQuantity="" UnderrunQuantity=""
 QuantityUOM="PR" RetailPrice="9.99" SellingPrice="9.99" SKU="8060804465DX216" UPC="883241501280" Size="6.5 B"
SizeDescription="50365" Style="8060804465DX" StyleDescription="" Color="BLK MULT/B" ColorDescription=""
ItemWeight="" ItemWeightUOM="" GrossWeight="" GrossWeightUOM="" GrossVolume="" GrossVolumeUOM="" Barcode="883241501280"
 BarcodeFormat="UP" AssortmentItemId="" PrePackFactor="" PiecesPerCarton="" LotNumber="" TicketType="PLO"
TicketLine="" ItemNumber="001-001000020" ItemDescription="FALL II 2008" ItemType="" Case1AssortmentGroupNumber="76777"
Case1AssortmentItemNumber="AAF" Case1Type="CARTON" Case1ItemQuantity="1" Case1Count="60" Case2AssortmentGroupNumber=""
Case2AssortmentItemNumber="" Case2Type="" Case2ItemQuantity="" Case2Count="" Case3AssortmentGroupNumber=""
Case3AssortmentItemNumber="" Case3Type="" Case3ItemQuantity="" Case3Count="" OriginalSystemSubLineReference="0002"
UnitCode="PR" SortSequence="" Attribute4="001000020" Attribute5="59" Attribute14="001000020" Attribute15="59" EstimatedArrivalDate="" MilestoneCategoryId="" TargetCategoryId="">
- <VariableDataSet>
  <VariableDataElement Code="290" Name="Carrier Name" Value="Ocean" />
  <VariableDataElement Code="370" Name="Model" Value="LYZBETH" />
  <VariableDataElement Code="380" Name="Brand" Value="Women's Chaps Footwear" />
  <VariableDataElement Code="400" Name="Fabrication" Value="GLEN PLAID/PATENT PU" />
  <VariableDataElement Code="410" Name="Buyer Season" Value="Fall 2008" />
  <VariableDataElement Code="440" Name="Concept Code" Value="FALL II 2008" />
  <VariableDataElement Code="460" Name="Merchandising Division" Value="Women's Chaps Footwear" />
  <VariableDataElement Code="580" Name="Landed Cost" Value="9.99" />
  <VariableDataElement Code="590" Name="First Cost" Value="9.99" />
  <VariableDataElement Code="620" Name="Manufacturers Suggested Retail" Value="59" />
  <VariableDataElement Code="670" Name="PO Style/Size Sequence Number" Value="001000020" />
  <VariableDataElement Code="750" Name="Prepack Indicator" Value="8060804465DXAAF" />
  <VariableDataElement Code="790" Name="UPC Code" Value="883241679910" />
  </VariableDataSet>
  </LineItem>
+ <LineItem LineItemId="2241856" ItemId="001000025-883241501297" Quantity="60" OverrunQuantity=""
UnderrunQuantity="" QuantityUOM="PR" RetailPrice="9.99" SellingPrice="9.99" SKU="8060804465DX217"
UPC="883241501297" Size="7 B" SizeDescription="50385" Style="8060804465DX" StyleDescription="" Color="BLK MULT/B"
ColorDescription="" ItemWeight="" ItemWeightUOM="" GrossWeight="" GrossWeightUOM="" GrossVolume="" GrossVolumeUOM=""
Barcode="883241501297" BarcodeFormat="UP" AssortmentItemId="" PrePackFactor="" PiecesPerCarton="" LotNumber=""
TicketType="PLO" TicketLine="" ItemNumber="001-001000025" ItemDescription="FALL II 2008" ItemType=""
Case1AssortmentGroupNumber="76777" Case1AssortmentItemNumber="AAF" Case1Type="CARTON" Case1ItemQuantity="1"
Case1Count="60" Case2AssortmentGroupNumber="" Case2AssortmentItemNumber="" Case2Type="" Case2ItemQuantity=""
Case2Count="" Case3AssortmentGroupNumber="" Case3AssortmentItemNumber="" Case3Type="" Case3ItemQuantity="" Case3Count=""
OriginalSystemSubLineReference="0003" UnitCode="PR" SortSequence="" Attribute4="001000025" Attribute5="59"
Attribute14="001000025" Attribute15="59" EstimatedArrivalDate="" MilestoneCategoryId="" TargetCategoryId="">
- <VariableDataSet>
  <VariableDataElement Code="290" Name="Carrier Name" Value="Ocean" />
  <VariableDataElement Code="370" Name="Model" Value="LYZBETH" />
  <VariableDataElement Code="380" Name="Brand" Value="Women's Chaps Footwear" />
  <VariableDataElement Code="400" Name="Fabrication" Value="GLEN PLAID/PATENT PU" />
  <VariableDataElement Code="410" Name="Buyer Season" Value="Fall 2008" />
  <VariableDataElement Code="440" Name="Concept Code" Value="FALL II 2008" />
  <VariableDataElement Code="460" Name="Merchandising Division" Value="Women's Chaps Footwear" />
  <VariableDataElement Code="580" Name="Landed Cost" Value="9.99" />
  <VariableDataElement Code="590" Name="First Cost" Value="9.99" />
  <VariableDataElement Code="620" Name="Manufacturers Suggested Retail" Value="59" />
  <VariableDataElement Code="670" Name="PO Style/Size Sequence Number" Value="001000025" />
  <VariableDataElement Code="750" Name="Prepack Indicator" Value="8060804465DXAAF" />
  <VariableDataElement Code="790" Name="UPC Code" Value="883241679910" /> </VariableDataSet>
  </LineItem>
+ <LineItem LineItemId="2241857" ItemId="001000030-883241501303" Quantity="60" OverrunQuantity="" UnderrunQuantity="" QuantityUOM="PR" RetailPrice="9.99" SellingPrice="9.99" SKU="8060804465DX218" UPC="883241501303" Size="7.5 B" SizeDescription="50405" Style="8060804465DX" StyleDescription="" Color="BLK MULT/B" ColorDescription="" ItemWeight="" ItemWeightUOM="" GrossWeight="" GrossWeightUOM="" GrossVolume="" GrossVolumeUOM="" Barcode="883241501303" BarcodeFormat="UP" AssortmentItemId="" PrePackFactor="" PiecesPerCarton="" LotNumber="" TicketType="PLO" TicketLine="" ItemNumber="001-001000030" ItemDescription="FALL II 2008" ItemType="" Case1AssortmentGroupNumber="76777" Case1AssortmentItemNumber="AAF" Case1Type="CARTON" Case1ItemQuantity="1" Case1Count="60" Case2AssortmentGroupNumber="" Case2AssortmentItemNumber="" Case2Type="" Case2ItemQuantity="" Case2Count="" Case3AssortmentGroupNumber="" Case3AssortmentItemNumber="" Case3Type="" Case3ItemQuantity="" Case3Count="" OriginalSystemSubLineReference="0004" UnitCode="PR" SortSequence="" Attribute4="001000030" Attribute5="59" Attribute14="001000030" Attribute15="59" EstimatedArrivalDate="" MilestoneCategoryId="" TargetCategoryId="">
- <VariableDataSet>
  <VariableDataElement Code="290" Name="Carrier Name" Value="Ocean" />
  <VariableDataElement Code="370" Name="Model" Value="LYZBETH" />
  <VariableDataElement Code="380" Name="Brand" Value="Women's Chaps Footwear" />
  <VariableDataElement Code="400" Name="Fabrication" Value="GLEN PLAID/PATENT PU" />
  <VariableDataElement Code="410" Name="Buyer Season" Value="Fall 2008" />
  <VariableDataElement Code="440" Name="Concept Code" Value="FALL II 2008" />
  <VariableDataElement Code="460" Name="Merchandising Division" Value="Women's Chaps Footwear" />
  <VariableDataElement Code="580" Name="Landed Cost" Value="9.99" />
  <VariableDataElement Code="590" Name="First Cost" Value="9.99" />
  <VariableDataElement Code="620" Name="Manufacturers Suggested Retail" Value="59" />
  <VariableDataElement Code="670" Name="PO Style/Size Sequence Number" Value="001000030" />
  <VariableDataElement Code="750" Name="Prepack Indicator" Value="8060804465DXAAH" />
  <VariableDataElement Code="790" Name="UPC Code" Value="883241679911" />
  </VariableDataSet>   </LineItem>
</LineItemSet>
 
Old March 17th, 2008, 06:12 AM
Authorized User
  
Join Date: Feb 2008
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default
Can anyone help me with this?Pls
 
Old March 17th, 2008, 06:38 AM
mhkay's Avatar
Wrox Author
  
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default
Elimination of duplicates is a special case of the problem called "grouping". In XSLT 2.0 it's generally done using the xsl:for-each-group construct, in 1.0 it's done using Muenchian grouping which is described in any good XSLT textbook or at www.jenitennison.com/xslt/grouping.

Either way, the special difficulty in this case will be constructing a grouping key. It's not entirely clear to me from your description what the grouping key should be, but it's probably a concatenation of various attributes of selected VariableDataElement children.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference
 
Old March 17th, 2008, 06:42 AM
mhkay's Avatar
Wrox Author
  
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default
> Can anyone help me with this?Pls

And please don't be impatient. There are only three or four people answering questions on this forum, they aren't necessarily in your timezone, and they all have other jobs which get higher priority. Most people who answer questions on public forums are less inclined to answer a question if the asker says it's urgent, because this often means the asker won't take time to study the answer properly.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference
 
Old March 17th, 2008, 07:11 AM
Authorized User
  
Join Date: Feb 2008
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default
Sorry about that.Actually i did not see your reply above.
 
Old March 17th, 2008, 07:16 AM
Authorized User
  
Join Date: Feb 2008
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default
If a key is made usign the above technique, will it take care of not having duplicate values for a combination of attribute values.
 
Old March 17th, 2008, 08:08 AM
Friend of Wrox
  
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default
Here is an example in XSLT 2.0 using xsl:for-each-group:
Code:
  <xsl:template match="LineItemSet">
    <xsl:for-each-group
      select="LineItem"
      group-by="concat(VariableDataSet/VariableDataElement[@Name = 'Prepack Indicator']/@Value,
                       '_',
                       VariableDataSet/VariableDataElement[@Name = 'UPC Code']/@Value)">

      <group key="{current-grouping-key()}" item-count="{count(current-group())}"/>
    </xsl:for-each-group>
  </xsl:template>
 
Old March 17th, 2008, 08:27 AM
Authorized User
  
Join Date: Feb 2008
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default
Thanks Martin.But since i need to work on XSLT 1.0, i wont be able to incorporate this since it would not support the for-each-group.

Thanks.
 
Old March 17th, 2008, 08:40 AM
Friend of Wrox
  
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default
Well so far you hadn't told us which version you want to use.
If you use XSLT 1.0 then use Muenchian grouping, here is a sample:
Code:
<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">

  <xsl:output method="xml" indent="yes"/>

  <xsl:key name="group"
           match="LineItem"
           use="concat(VariableDataSet/VariableDataElement[@Name = 'Prepack Indicator']/@Value,
                       '_',
                       VariableDataSet/VariableDataElement[@Name = 'UPC Code']/@Value)"/>

  <xsl:template match="LineItemSet">
    <xsl:copy>
      <xsl:apply-templates
        select="LineItem[generate-id() = generate-id(key('group', concat(VariableDataSet/VariableDataElement[@Name = 'Prepack Indicator']/@Value,
                       '_',
                       VariableDataSet/VariableDataElement[@Name = 'UPC Code']/@Value))[1])]"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="LineItem">
    <group key="{concat(VariableDataSet/VariableDataElement[@Name = 'Prepack Indicator']/@Value,
                       '_',
                       VariableDataSet/VariableDataElement[@Name = 'UPC Code']/@Value)}"
           item-count="{count(key('group', concat(VariableDataSet/VariableDataElement[@Name = 'Prepack Indicator']/@Value,
                       '_',
                       VariableDataSet/VariableDataElement[@Name = 'UPC Code']/@Value)))}"/>
  </xsl:template>
</xsl:stylesheet>
 
Old March 17th, 2008, 09:12 AM
Authorized User
  
Join Date: Feb 2008
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default
Thanks Martin.

Sorry if this seems like a silly question.I just wanted to know will this give me unique set of values without duplicates.
Page 1 of 2 1 2





Similar Threads
Thread Thread Starter Forum Replies Last Post
getting distinct values from attribute markus2000 XSLT 1 June 13th, 2006 03:06 AM
Unable to fetch textbox values triveni_sh BOOK: Professional Crystal Reports for VS.NET 0 January 5th, 2006 01:28 AM
how to fetch records from website avats ADO.NET 0 November 3rd, 2005 03:45 PM
Distinct values of data arnabghosh Access 18 September 13th, 2005 05:41 PM
Field Specific DISTINCT Statement tdaustin Classic ASP Basics 1 March 30th, 2004 12:06 AM




Contact Us - Wrox - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.