Liquid Studio 2020
Data Mapping / Data Mapper Functions / By Category / Set / GroupBy
In This Topic
    GroupBy
    In This Topic

    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>