ShowTable of Contents
In Mashup Center V2 the data mashup Group operator now supports functions on the group element and on the associated data elements.This article uses the example GPA Query
feed to show how a data mashup can use functions to group by the numeric GPA and to also group by the letter grade (A, B, C, D or F).
In Mashup Center V1 the ability to execute functions was available only in the Transform and Filter operators. In V2, functions are available in six operators: Filter, Foreach, Group, Merge, Sort, and Transform.
This article uses the example GPA Query
feed to show how to use functions in the Group operator to group on the GPA range. It also shows how to nest functions so that the group occurs on the letter grade instead of the numeric GPA.
Import the GPA Query feed
The GPA Query
feed is installed with Mashup Center.
First, create a new data mashup, add a Source operator, and import the GPA Query
feed from the Mashup Center catalog. Click on the Advanced tab. The Feed Type is set to ATOM by default. Change it to XML and reset the Repeating Element to the data row as shown below:
Click on the Preview tab to preview the data. The first entry for John Doe is expanded below:
John's GPA is 2.7.
We want to group students by GPA ranges, with 4.0 being an A, 3.0-3.9 a B, 2.0-2.9 a C, 1.0-1.9 a D, and anything below 1 an F. John should be listed in the group of C students.
Group by GPA
This section shows how to group the data by the numeric GPA. A later section will show how convert this to a letter grade and group by the letter grade instead.
- Add a Group operator to the canvas
- Right-click on the down-arrow for Group expression, choose Specify a function value.
- Choose the Substring Before function:
- For the Input, select Specify a value from the Input tree, then select the text beneath the GPA element.
- For the Search String, enter a dot (.). This will select all text up to the dot. For example, 2.7 will be returned as 2.
- Click on the OK button.
- Right-click on the down-arrow for Associated data expression, choose select Specify a value from the Input tree, then choose the row element.
The Group operator should look something like the picture below:
Click on Preview to see the results. In the image below, just the A ("4") and B ("3") groupings are expanded to show that one student has an A and seven have a B:
Expanding some of the rows for the B students lets us verify that the groupings are correct:
- The result of the group expression is output to an element named genval1. We can rename genval1 with a Transform operator later.
- Results aren't ordered. We can add a Sort operator later to sort on the genval1 element.
- These results show the numeric grouping, but we really want to see grouping by letter grade. We'll tackle that in the next section.
Group by Letter Grade
To group by the letter grade, refine the function invocation in the group expression by nesting the Substring Before function call inside the Translate function, which converts the numeric result to alphabetic, as shown in the picture below:
The Translate function converts characters in the Map String argument by position in the Translate String argument; so, 4 is converted to A, 3 to B, and so on. The picture below shows output with the letter grade:
This article focused on just the Group operator, showing how to group on a function call.
More refinements to the data mashup are possible, such as sorting on the genval1 element with the Sort operator and renaming the genval1 element using the Transform operator.