Executing queries¶
One of the key property of Rulex Factory Data Manager task is the possibility to execute SQL-like queries on the underlying dataset through simply drag and drop operations.
All these operations are performed in the Query Manager. General overview about this pane has been given in the data tab page.
Here we are going to concentrate on the general approach to query construction and to a more detailed description of the five areas composing the Query Manager.
Procedure
On the Attribute list select a list of attributes and drag one of its chips onto one of the five areas of the Query Manager. Attribute chips are going to be shown in the dropped area selected
If Autocommit is enabled the query will be automatically executed; otherwise you have to press the Play button to execute it.
Once query is executed, total number of displayed row may differ as well as their ordering. Click Clear to reset the query or Make persistent to definitely apply it, by cutting off all the not displayed lines and by re-ordering the whole dataset.
Take into account any of the presented operations can be undone and redone at any moment.
Filter query¶
In Pre-filter and Post-filter areas the user is able to construct complex rules to filtering the Data Manager underlying dataset.
Note
The difference between these two areas is only limited to the computation order therefore we are going to present properties and interactions only one time for both.
When an attribute is dragged from the Attribute list, and it is dropped in one of these areas, a new line is created next to the dropped position. Nearest line background will change during drag hovering to highlight the point of the new created condition.
Each line contains a single attribute, indicated by the attribute chip and contains a single condition.
Tip
By hovering with the mouse an attribute chip, a tooltip containing the code of the underlying condition, if any, is shown.
Conditions can be combined using AND / OR logical operators to construct more complicated rules.
Filter condition nesting¶
To perform even more complicated nested constraints, condition lines are organized in nested levels, as in Figure Filter condition nesting. Each level will contains a set of conditions linked together by a unique AND or OR operator. The type of operator connecting a single level is presented at the left of the block itself (see Figure Filter condition nesting).
Note
To change the type of operator of a single level you can click on it. At any click it will switch between the AND, and the OR value.
Once a single new attribute is dropped, a condition panel will appear on the screen. In this panel the user can configure all the properties of the single condition and at the end he can confirm the addition of the same to the constraint tree.
Condition panels¶
The graphical structure of the condition panel differs according to the attribute type:
For nominal or binary attributes description of the condition panel is present in this section.
For continuous, integer, percentage or currency attributes description of the condition panel is present in this section.
For date, week, month, quarter or datetime attributes description of the condition panel is present in this section.
For time attributes description of the condition panel is present in this section.
Conditions already created on the tree can be selected by left-clicking on any point of their connection line. A green background will highlight you lines are effectively selected.
Tip
By pressing Del button with an active selection, you are going to delete in a bulk operation all the selected condition lines.
Group query¶
When a list of attributes is dragged from the Attribute list, and it is dropped in Group area, the same list of attribute chips will be shown inside performing a group operation on the underlying dataset.
By dropping a list of n attributes onto the Group area, Rulex Factory performs an operation which mimics the behavior of the SQL GROUP clause: it retains as displayed a single row for any occurrence of distinct n-tuple of values coming from the considered columns.
Note
Differently from SQL GROUP clause, anything is applied directly on the dataset. Rulex Factory Group operation performs a filtering operation reducing only the number of displayed rows and modifying the ordering to group together same value rows.
Since any formula operation applies only on the displayed set of rows there are situations where the user may need to expand the group operation to show all the lines simply reordered to take care of the grouping definition. In these situations you can click on the left sidebar of the Group area to change the Expand mode:
Expand less: only the first row of each group is displayed.
Expand more: all the lines are displayed.
By right-clicking on any attribute populating the Group area you will open the following context menu:
Disable: disable all the selected attributes which are going to appear greyed out in the Group area. This could be useful to study some what-if scenario.
Delete: remove all the selected attributes from the Group area.
Tip
By pressing Del button with an active selection, you are going to delete in a bulk operation all the attributes.
Apply query¶
When an attribute is dropped onto the Apply area, a new line is added to the list already present.
Rulex Factory Apply operation apply an aggregator function to the selected column, by taking automatically into account eventual grouping mechanism or row filtering.
Once the attribute is dropped a dedicated panel will appear to allow the user to select the proper aggregator function.
Note
Available function differs according to the attribute type.
For nominal and binary attributes, they are:
For integer, continuous, percentage and currency attributes, they are:
For date, week, month, quarter, time and datetime attributes, they are:
The aggregator function chosen is reported with an acronym on the left side of the attribute line. By clicking on it the same panel is re-opened to enable the user to modify it once attribute line is already been created.
Tip
By pressing Del button with an active selection, you are going to delete in a bulk operation all the attributes.
If you are right-clicking on any attribute populating the Apply area, you will open the following context menu:
Open: open the aggregator function panel.
Disable: disable all the selected attributes which are going to appear greyed out in the Apply area. This could be useful to study some what-if scenario.
Delete: remove all the selected attributes from the Apply area.
Sort query¶
When a list of attributes is dragged from the Attribute list, and it is dropped in Sort area, the same list of attribute chips will be shown inside performing a group operation on the underlying dataset.
By dropping a list of n attributes onto the Group area, Rulex Factory performs a sort operation on the whole dataset: dataset is ordered on the first attribute, then it orders same value ranges using the second attribute and so on.
For any sort operation you are free to decide the sorting order (descending or ascending). The order is then displayed on the left side of each attribute line:
Ascending
Descending
By clicking on these icons in the Sort area you can invert the sorting order for one particular attribute.
Tip
By pressing Del button with an active selection, you are going to delete in a bulk operation all the attributes.
If you are right-clicking on any attribute populating the Sort area, you will open the following context menu:
Sort ascending: change the sorting order of all the selected attributes to ascending.
Sort descending: change the sorting order of all the selected attributes to descending.
Disable: disable all the selected attributes which are going to appear greyed out in the Sort area. This could be useful to study some what-if scenario.
Delete: remove all the selected attributes from the Sort area.
Note
You can immediately understand which attributes are sorted even by looking at header row in the main spreadsheet. A sort icon will be shown at the right end of any header chip of a sorted attribute.