May 2011 - Posts

BizTalk Mapper - resolving decimal sum and separator issues, Keyed Cumulative Sum continued…
18 May 11 07:59 PM | Johan Hedberg

As a continuation of my Keyed Cumulative Sum post, where I did a key based summation using an xslt call template in a BizTalk map I got two main points of feedback worth repeating and showing.

Questions

  1. If I use Sum on a decimal number it gives me all sort of weird decimals in the Sum although it shouldn’t; ie a sum of 1.1 and 1.1 could result in 2.199999999999. How do I solve that?
  2. What if I am in Sweden (or Germany or some other country) and I want to use a comma (,) as a decimal separator instead of a period (.). How can I do that?

Answers

  1. You can use the xslt format-number function to remove erroneous decimal digits that are the result of using Sum on a decimal number.
  2. You can use the xpath translate function to replace a period with a comma. However this does not conform to an xsd:decimal, so make sure that the target schema does not have this field defined as a decimal if you wish to be compliant.

This will make the outlook of the xslt instead look like this (code is edited to improve readability):

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:param name="param2" />
  <xsl:element name="Compensation_Amount">
    <xsl:variable name="SumOfNodes" select="//row[Compensation_SubCode=$param1 and   
Compensation_Level=$param2]/Compensation_Amount)"
/> <xsl:variable name="FormattedSum" select="format-number($SumOfNodes,'0.00')"/> <xsl:value-of select="translate($FormattedSum, '.', ',')" /> </xsl:element> </xsl:template>

The use of several variables are for maintainability only, and are not required. You could jam it all into the value-of select statement if you really want to.

/Johan

Filed under:
Yes, that’s my face in the paper today
03 May 11 07:00 AM | Johan Hedberg | 1 comment(s)

Annons1

In essence it says that we are looking for new recruits in the Microsoft (Integration) area. Great opportunities for the right person.

Also see http://www.zystems.se/

Filed under: ,
BizTalk Mapper - Keyed Cumulative Sum
02 May 11 11:52 PM | Johan Hedberg | 4 comment(s)

From time to time I get questions about mapping puzzles, and I love it! Send me more of those! (I realize might regret that statement later). This time the puzzle was how best to do a conditional cumulative sum, or keyed conditional sum, also possibly known as grouped conditional sum.

The scenario is this (I’ve removed some namespaces etc for readability):

<Compensation>
  <rows>
    <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>25288084</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
  <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>29627</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
    <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>2</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>234348</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
  </rows>
</Compensation>

How do you best produce a map that as step one summarize all Compensation_Amount for each variation of Compensation_SubCode, and returns this (let’s call this scenario 1):

<Compensation>
  <rows>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Amount>25552059</Compensation_Amount>
    </row>
  </rows>
</Compensation>

and as step two, summarize all Compensation_Amount for each combination of Compensation_SubCode and Compensation_Level, and return this (let’s call this scenario 2):

<Compensation>
  <rows>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Amount>25317711</Compensation_Amount>
    </row>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>2</Compensation_Level>
      <Compensation_Amount>234348</Compensation_Amount>
    </row>
  </rows>
</Compensation>

You might start thinking custom xslt and xsl:sort or xsl:keys or muenchian methods and things like that. That’s one way to do it. I actually prefer using the mapper for most maps until performance forces me to go another way.

My solution for scenario 1:

clip_image001

My Solution for scenario 2:

clip_image002

I am not presenting a complete solution for scenario 1, since it’s really just a subset of the functionality required for scenario 2, but you should get the point from the explanation of scenario 2.

The magic of the scenario 2 solution happens in the three scripting functoids. The first one, connected to the destination root node contains the following snippet (again namespace deprived for readability):

List<string> keyList= new List<string>();
void initglobals() {}

It’s simply responsible for making sure that the variable keyList is set up. The empty method that return void ensures that I can connect this to an output node to make sure it triggers, yet it will produce no output.

The next functoid then makes use of that list, and adds all unique keys to it (which is unique combinations of the two keys separated by a secure character, in this case a colon):

public string AddKey(string key)
{
  if (keyList.Contains(key))
    return string.Empty;
  keyList.Add(key);
  return key;
}

The Not Equals functoid then makes sure that we will only see output when a new unique combination is found, by checking to make sure that the string outputted from the scripting functoids <> and empty string.

The third functoid then is responsible for doing the summation (I’ve inserted a linebreak and some spaces in the xpath statement for readability, so don’t just copy paste this):

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:param name="param2" />
  <xsl:element name="Compensation_Amount">
  <xsl:value-of select="sum(//row[Compensation_SubCode=$param1 
              and Compensation_Level=$param2]/Compensation_Amount)" />
  </xsl:element>
</xsl:template>

This simple xslt call template takes the two key values in, and does a summation of all rows that has these key values set to these specific values.

Now someone might object and say that this would mean an unnecessary amount of summation gets done. But that’s actually not the case, the mapper is clever enough to only do this under the condition of the Not Equals functoid returning true, as the following xslt snippet shows (this is just a portion of the xslt generated):

<xsl:variable name="var:v3" select="userCSharp:LogicalNe(string($var:v2) , &quot;&quot;)" />
<xsl:if test="$var:v3">
  <row>
    ...
    <xsl:call-template name="OutputSum">
    <xsl:with-param name="param1" select="string(Compensation_SubCode/text())" />
    <xsl:with-param name="param2" select="string(Compensation_Level/text())" />
    </xsl:call-template>
  </row>
</xsl:if>
’So a short summation (pun intended) of the work involved:

1. Create a global variable, a list to hold keys

2. Add to that list once you discover a new key

3. Add logic to that discovery to only output your destination node when you hit a new key

4. Implement a xslt call template and use xpath to sum based on your keys

Filed under:

This Blog

News

    Messenger

    Twitter Updates

      Follow me on twitter

      Visitors

      Feedburner Subscribers

      Locations of visitors to this page

      Disclaimer

      All material is provided AS IS voiding any thinkable or unthinkable effect it might have for any use whatsoever. There... is that clear enough ;)

      Pages

    Syndication