Function Name |
GroupBy | ||||||
Category |
Set | ||||||
Icon |
 |
||||||
Description |
Groups a number of records according to one or more fields | ||||||
Inputs |
|
||||||
Outputs |
|
||||||
Properties |
|
||||||
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> |
|