Liquid XML Studio 2019
GroupBy
Data Mapping > Data Mapper Functions > By Category > Set > GroupBy

Function Name
GroupBy
Category
Set
Icon
Description
Groups a number of records according to one or more fields
Inputs
Nodes The nodes to be grouped
Group By Value x The values to group the Nodes by (dynamic, right click to add more)
Outputs
Group An entry for each distinct set of 'Group By Value x' values
   Group By Value x The distinct grouping values for this parent 'Group'
   Row All the input 'Node' entries that have matching 'Group By Value x' values
Properties
None -

Usage

The Data Mapper Group By function makes it possible to impose a hierarchy over data delivered in a flat format. This is typically most useful when working with CSV data, but can be used on any data source. The function works by taking an input set of nodes, the values that the nodes are to be grouped by are then specified by connecting values to the 'Group By Value' inputs. If more than one value is to be grouped by then additional entries can be added by using the right click context menu. All the input nodes are then read grouped according to the values in the 'Group By Value' inputs. The result is a Group entry for each distinct set of 'Group By Value' values,  within this are all the values being grouped, and all the rows that match the group values.

 

Example 1 - building a single level hierarchy

Given the following CSV data.

Source Data
Copy Code
Customer ID,    Customer Name,  Order ID,   Order Price
1,              Fred,           100,        0.55
1,              Fred,           101,        3.48
2,              Jane,           102,        2.10
2,              Jane,           103,        1.93

 

If we wanted to represent it as a hierarchical structure in XML, then we would first need to group the values 'Customer ID' & 'Customer Name'.

 

This would give us give us a Group entry for each distinct 'Customer ID', 'Customer Name' value in the source data, and a Row entry for every source node that has the corresponding 'Customer ID', 'Customer Name' values. This allows us to construct the simple hierarchy.

Resulting XML
Copy Code
<!--Created by Liquid Data Mapper Libraries (www.liquid-technologies.com) for Liquid Technologies Ltd -->
<Breakfast>
    <Customer ID="1">
        <Name>Fred</Name>
        <Order ID="100">
            <Price>0.55</Price>
        </Order>
        <Order ID="101">
            <Price>3.48</Price>
        </Order>
    </Customer>
    <Customer ID="2">
        <Name>Jane</Name>
        <Order ID="102">
            <Price>2.10</Price>
        </Order>
        <Order ID="103">
            <Price>1.93</Price>
        </Order>
    </Customer>
</Breakfast>

 

Example 2 - building a multi level hierarchy

If the source data contains detail about each item in an order then we can create a second level in the hierarchy.

Source Data
Copy Code
Customer ID,    Customer Name,  Order ID,   Item Description,   Item Price
1,              Fred,           100,        Bread,              0.35
1,              Fred,           100,        Jam,                0.20
1,              Fred,           101,        Pancakes,           1.99
1,              Fred,           101,        Bacon,              0.99
1,              Fred,           101,        Syrup,              0.50
2,              Jane,           102,        Granola,            1.35
2,              Jane,           102,        Milk,               0.75
2,              Jane,           103,        Bread,              0.35
2,              Jane,           103,        Bacon,              0.99
2,              Jane,           103,        Sausage,            0.59

 

We still need to group the values 'Customer ID' & 'Customer Name'. This gives us all the orders for each customer, we then need to group the remaining entries by Order ID to give us the items in each order.

 

The resulting XML is a hierarchical model of the data.

Resulting XML
Copy Code
<Breakfast>
    <Customer ID="1">
        <Name>Fred</Name>
        <Order ID="100">
            <Item>
                <Description>Bread</Description>
                <Price>0.35</Price>
            </Item>
            <Item>
                <Description>Jam</Description>
                <Price>0.20</Price>
            </Item>
        </Order>
        <Order ID="101">
            <Item>
                <Description>Pancakes</Description>
                <Price>1.99</Price>
            </Item>
            <Item>
                <Description>Bacon</Description>
                <Price>0.99</Price>
            </Item>
            <Item>
                <Description>Syrup</Description>
                <Price>0.50</Price>
            </Item>
        </Order>
    </Customer>
    <Customer ID="2">
        <Name>Jane</Name>
        <Order ID="102">
            <Item>
                <Description>Granola</Description>
                <Price>1.35</Price>
            </Item>
            <Item>
                <Description>Milk</Description>
                <Price>0.75</Price>
            </Item>
        </Order>
        <Order ID="103">
            <Item>
                <Description>Bread</Description>
                <Price>0.35</Price>
            </Item>
            <Item>
                <Description>Bacon</Description>
                <Price>0.99</Price>
            </Item>
            <Item>
                <Description>Sausage</Description>
                <Price>0.59</Price>
            </Item>
        </Order>
    </Customer>
</Breakfast>