Table of Contents ... | ... Table of Code Examples |
<< Lesson 08. Data types, op. table, smaller topics. | Lesson 10. Aggregations >> |
With this lesson the content gets slightly more complex :oops: . And I have to admit: In a way more experimental as it is pretty uncovered ground.
You probably know the term and the meaning from SQL-queries, so you can guess where we are heading to. For full usage of groupings aggregations are required also, but let us begin with basic groupings as with the following easy example:
~*/SearchResFlightRel/FlightBookRel$(@AGENCYNUM,UPPER(@SMOKER))
Code Example 37,$(@AGENCYNUM,UPPER(@SMOKER)), basic groupings
The $ is now attributed with a list of group elements (agencynum and smoker). Please note that the group element is not necessarily an attribute, any expression is valid. New entries are written to the result table only in case the combination of group elements in the actual data set did not already appear in the result table yet. The result table will contain the first entry of every group. In the chosen example a list of 4116 entries is condensed to about 70 entries.
The standard behaviour of the grouping adds the grouping conditions to the result table automatically. The naming of the group conditions follows the following rules:
In the examples above the two attribute would be named AGENCYNUM and F_SMOKER.
In some cases it might be required to suppress the automatic adding of the group condition (e.g. it is added with different naming manually in the init part, see next chapter). This is possible with an exclamation mark after the dollar as in the following example:
~*/SearchResFlightRel/FlightBookRel$!(@AGENCYNUM,@SMOKER){!DEMO=@AGENCYNUM&@SMOKER}
Code Example 38,$!(@AGENCYNUM,UPPER(@SMOKER)), basic grouping without group condition takeover
The grouping syntax is now extended with the possibilities to build assignments based on the group data.
The syntax is as follows: $(ListGroupIdentifier:Initblock:Loopblock)
The following example:
~*[!AVG]/SearchResFlightRel/FlightBookRel$(@FORCURKEY:!Sum=0;!Amt=0:!Sum=!Sum+1;!Amt=!Amt+@LOCCURAM;!AVG=!Amt%!Sum)
Code Example 39,$(@FORCURKEY:!X=0:!X=!X+1), hand made grouping aggregations
groups the bookings according to the foreign currency and calculates the number of bookings in this curreny, the complete amount (in USD) and the average amount (also in USD). Please note that !AVG never appears on the right side of assignment hence it must not be initialized.
Limitations:
~*/SearchResFlightRel/FlightBookRel$(*:!Sum=0:!Sum=!Sum+1)
Code Example 40,$(*:!X=0:!X=!X+1), group to a structure
As shown in the example above it is also possible to group the complete content to one row. There is a technical difference compared to the other examples, since the result here is a structure with the field sum, and not a table.
Note: the original example here tried to group to an INT2 field (see unstructured returns). Unfortunately, this is not working since it is not possible to access unstructured returns for reading, which is required for aggregations.
There is the plan to exploit this feature in future together with an enhanced sub()-handling.
With the work on groups I realized that the way to realize enumerations is rather unhandy. Consequently I decided to spend a new feature aiming to assist the user in generating lists.
List has the following syntax: LIST(target,separator,content).
See also the example, note that the := assignment is required here:
~STRING/SearchResFlightRel/FlightBookRel{!Ret:=LIST(!Ret,",",@PASSNAME)}$(*)
Code Example 41,{!X:=List(!X,",",@Feld)}, LIST function
The list function has the following characteristics:
Round and Int are using the ABAP round function with the modes ROUND_HALF_UP and ROUND_DOWN. The second parameter - if available - will be handed over as precision.
Returns log respectively exponential value of parameter 1. If second parameter is provided it will be used as basis, otherwise e.
You want to know why the heck one needs logarithm in a business application? Well, I'll show you:
~*/SearchResFlightRel/FlightBookRel$(int(Log(@LOCCURAM,10)):!LMIN=round(exp(int(Log(@LOCCURAM,10)),10),2);!LMAX=round(exp(1+int(Log(@LOCCURAM,10)),10),2):!CNT=Count())
Code Example 42,$(int(Log(@LOCCURAM,10)): Log Function and logged grouping
It looks a bit more complex as it is. Try it out and and take a look on the result, which speaks for itself.Well, to explain it nevertheless. We group to the whole value of the decimal logarithm of our base value. This means nothing else, then the number of digits of the dollar amount. The LMIN and the LMAX value simply calculate the boarders of our area, in our example this is probable [100 .. 1000] and [1000 .. 10000]. Note that the border calculation is part of the init block, hence it will be calculated only one time for every group.The example here might be a bit artificial, nevertheless the concept might be useful to group customers/products/projects where the range is between some Pennys to millions of bucks.
Several functions deal with the possibility that a sub function may return no result, i.e. an initial reference. The above methods are designed to catch that problem or react on it.
The comparison functions were also enhanced to allow a comparison against the INITIAL-value. Please note the following two points:
That's the content for lesson 9. In respect to lesson 10, I have to say: "no, we are not finished with aggregations. Not at all." :wink:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
43 | |
25 | |
17 | |
15 | |
11 | |
7 | |
7 | |
6 | |
6 | |
6 |