05.06.2015
Anton Grening
Author: Anton Grening

ACL An­a­lyt­ics™ – The Clas­si­fy work­around

In the first part of this series we got to know some practical ACL™ functions on text fields, and saw how easy it is to combine these fields.

As a rule it is best to save all information in separate columns (fields) so you can address it singly and analyze it without going "the long way round". But there are still cases where it makes more sense to save several items of information in the same field – like house number and street name. Or you may need to combine two fields in one so that you can analyze them together, as we did in the previous blog in the case of the address. So if you want to join two text fields to form one, you can do it quite simply in ACL™ by a plus sign (e.g. No. + Street) - what is called concatenation. However you must not forget the data type. The field name in this example may be "No." (for house number), but what we have is a text field because you are not likely to use it for calculations. Therefore this joining procedure requires that all fields to be combined are text. Text fields have a fixed length, meaning that the spaces are also used to fill the newly generated field (unless the text takes up the full field width). One possible approach to clean combination was shown in the previously posted blog. Today I want to show you how it could look in ACL™:

Fig. 1: Define new field c_No_street by combing the fields No and street

A more important reason to use this functionality is the fact that some commands like Classify can always only be used on one field. Meaning in this case that ACL™ can only group by one text field and produce the appropriate subtotals.

 

 

Fig. 2: Classify on Company_Code and subtotal Amount_in_Local_Currency

But if you now want to classify by company code for the fiscal year, it is not that straightforward because selection by dropdown only permits one field. For choice of subtotals there is a selection box, i.e. you have a choice of more than one field. In this example only the amount in local currency was chosen.

Of course it is possible to use several fields at the same time through the Summarize command. Although in many a case it is easier to use Classify, which automatically produces – as the above screenshot shows – Percent of Count/ Percent of Field columns. Plus, a diagram of analysis is generated. Neither of these is the case with Summarize. These are just a few of several possible reasons why you would want to use the Classify command on key fields in a combination.

And this is possible by a workaround – classifying by company code and at the same time by fiscal year. To do it you only have to combine the two text fields and save this as one field. You keep the newly computed field in the ACL™ project, and can use it for further analysis like Classify.

Fig. 3: Define new field c_ComCode_FYear and classify it

The field thus generated is now in the selection, and you can simply classify by both criteria in combination. The "Percent of Count" and "Percent of Field" columns are also generated as usual.

This in-between step makes workaround possible, doing away with the containment of the Classify command, and allowing simultaneous grouping to multiple text fields.

If you continue to work on the basis of the results table incidentally, it is also possible to separate fields again, like by SUBSTR() or SPLIT().

Fig. 4: Split combined field c_ComCode_FYear

These fields are then available again singly, and can be used for further steps.
Note: The operation shown in Fig. 5 is only possible, of course, if the field lengths originally defined in the table layout (in this example 4) are known and always the same (see details of space above).

The procedure for combining fields is admittedly nothing unusual, especially when working with SAP® data or multiple tables. Here you need key fields to uniquely identify data records. Just one field is often not enough, so you have to resort to combined fields.

Our next blog posting will go into this and show you how ACL™ manages to work an analysis across multiple tables.

I hope you were able to learn something from this posting, and can use it working with ACL™.

If you have questions or comments, feel free to contact us at info@dab-gmbh.de.

Read next part.


Comments (0)
Be the first who comments this blog entry.
Blog login

You are not logged in. Please log in to comment this blog entry.

go to Login