sententia
Home    Blog

Doing a Distinct Sort for Unique Values in Tibco BW

Here is the complete BW process

The Sort Mapper steps:
Drag the process data node to the activity input and the following wizard appears.  Do a 'For Each'

The 'next' will map the elements


Add a child element as a Sort and move it above the repeating node.  So I want to first sort by the name, then a secondary sort on the value
Repeat for secondary sort (but set the data type to Number)

Final look of the mapper


Now for the Remove Dups Mapper
Here is the input Editor


In the mapper, I need to surround the node with an IF statement so I can skip duplicates
($MyIdx=1) or (current()/MyName !=$Sort/root/MyNode[$MyIdx - 1]/MyName)
I always take the first one and then I compare each element with the prior element.  If they are not the same, we output it!



My sample input data set

<?xml version = "1.0" encoding = "UTF-8"?>
<root>
    <MyNode>
        <MyName>a</MyName>
        <MyValue>50</MyValue>
    </MyNode>
    <MyNode>
        <MyName>a</MyName>
        <MyValue>100</MyValue>
    </MyNode>
    <MyNode>
        <MyName>b</MyName>
        <MyValue>70</MyValue>
    </MyNode>
    <MyNode>
        <MyName>b</MyName>
        <MyValue>90</MyValue>
    </MyNode>
    <MyNode>
        <MyName>c</MyName>
        <MyValue>80</MyValue>
    </MyNode>
    <MyNode>
        <MyName>d</MyName>
        <MyValue>60</MyValue>
    </MyNode>
</root>

Output from the Sort Mapper
<?xml version = "1.0" encoding = "UTF-8"?>
<root>
    <MyNode>
        <MyName>a</MyName>
        <MyValue>50</MyValue>
    </MyNode>
    <MyNode>
        <MyName>a</MyName>
        <MyValue>100</MyValue>
    </MyNode>
    <MyNode>
        <MyName>b</MyName>
        <MyValue>70</MyValue>
    </MyNode>
    <MyNode>
        <MyName>b</MyName>
        <MyValue>90</MyValue>
    </MyNode>
    <MyNode>
        <MyName>c</MyName>
        <MyValue>80</MyValue>
    </MyNode>
    <MyNode>
        <MyName>d</MyName>
        <MyValue>60</MyValue>
    </MyNode>
</root>

output from RemoveDups Mapper
<?xml version = "1.0" encoding = "UTF-8"?>
<root>
    <MyDistinctNode>
        <MyName>a</MyName>
        <MyValue>50</MyValue>
        <MyPosition>1</MyPosition>
    </MyDistinctNode>
    <MyDistinctNode>
        <MyName>b</MyName>
        <MyValue>70</MyValue>
        <MyPosition>3</MyPosition>
    </MyDistinctNode>
    <MyDistinctNode>
        <MyName>c</MyName>
        <MyValue>80</MyValue>
        <MyPosition>5</MyPosition>
    </MyDistinctNode>
    <MyDistinctNode>
        <MyName>d</MyName>
        <MyValue>60</MyValue>
        <MyPosition>6</MyPosition>
    </MyDistinctNode>
</root>
TibcoImportTestDistinctSort.zip
(1.88 KB)