Add a Field¶

You can add fields from the current view or from the related views to the output schema. For each field added, a column will be created in the query results with its corresponding data.

We have already seen how to add fields and expanded fields using the Fields and Relationship fields panels. There are other ways:

• Drag-and-drop a field from the Order by panel.

• Select some fields in the Order by panel and drag-and-drop them all.

• Click the icon in the title of the Output columns panel and select the Add option. A dialog will appear to define a new output field. Select one field on the list. If you want to edit the name of the column in the query results, click the icon.

  

  Dialog to add a field to the Output columns panel¶

Set all Fields¶

If you want that the output schema of the query reflects the schema of the view, you can add all its fields to the Output columns as we have already seen. However, this approach has a problem. Let us imagine that you build a query in this way and save it for later. In the meantime, the schema of the view changes because someone added a new field. The query you saved will no longer reflect the schema of the view.

There is an alternative way of building the query that solves this problem:

1. Click the icon in the title of the Output columns panel.

2. Check the All fields option.

If you save a query like this and the schema of the view changes in the future, your query will automatically change too. This way, the output schema of your query will always reflect the schema of the view.

Add a Derived Field¶

You can create new fields by transforming or combining the available fields in the Fields and Relationship fields panels. For instance, from the firstname and lastname fields you can build a derived field with the value concat(firstname, ' ', lastname).

To build a derived field, you have to:

1. Click the icon in the title of the Output columns panel.

2. Select the Add option.

3. Click the icon and give the derived field a new name.

4. Write the expression that describes how to calculate the derived field.

Dialog to add a derived field to the Output columns panel¶

The editor counts with the following features to help you write an expression:

• Highlighting. Each component of an expression is highlighted with a different color: functions, operators, literals, fields, etc.

• Autocomplete. Press Ctrl + Space and a menu with suggestions will appear.

• Validation. Every time you type in the editor, the expression is validated. If the expression is correct, you will see a icon. If it is incorrect, you will see a icon. Put the mouse over the icon and a tooltip will explain why it is invalid.

Add a Derived Field using Recommendations¶

You can create new fields by using recommendations based on past activity of all the users. Transformations used to create fields in queries executed previously in Data Catalog will be recommended to build new queries.

To create derived fields using general recommendations for a view, you have to:

1. Click the icon in the title of the Output columns panel.

2. Select the Recommendations option.

3. Select one of the recommended transformations.

4. Write a new name for the derived field.

5. Replace ? if any by a constant value in Expression.

To create derived fields using specific recommendations for a view field, you have to:

1. Click the icon of a view field in the Fields panel.

2. Select the Recommended output expressions option.

3. Select one of the recommended transformations.

4. Write a new name for the derived field.

5. Replace ? if any by a constant value in Expression.

Dialog to add a derived field using recommendations¶

Another way to create derived fields based on recommendations is using the autocomplete feature when you are typing in the field expression editor.

Dialog to add a derived field based on recommendations using autocomplete feature¶

Add an Aggregation Field¶

A derived field usually works by transforming the data on a single tuple. Its expression takes the data of the tuple, apply some functions on them, and produces a new value. However, there is special case of functions that do not follow this pattern: aggregation functions. An aggregation function takes data from several tuples and produces a single value by combining them all. Examples of aggregation functions are MAX, SUM or AVG. A derived field that uses an aggregation function is called an aggregation field.

To build an aggregation field you have the following options:

• Click the icon of a field in Output columns and select the option Apply aggregation function. A menu will appear with all the aggregation functions you can apply to the field. Select one of them.

  

  A menu with all the aggregation functions you can apply on a field¶

• Create a derived field as explained before and write an expression that uses an aggregation function.

• Add the NUMBER_OF_RECORDS field from the Fields panel, since it is equivalent to the COUNT aggregation function.

Once the Output columns contains an aggregation field, the semantic of the query changes as follows:

1. The tuples of the view are partitioned in groups. For each distinct value of the non-aggregation fields, a group of tuples is generated with all the tuples that contain that value. In case all the fields in Output columns are aggregation fields, then one group is generated with all the tuples of the view.

2. For each group, aggregation fields are calculated by applying the corresponding aggregation function on the tuples of the group.

3. There will be one tuple in the query results for each group. The tuple will consist of:

   • The values of the non-aggregation fields that generated the group.

   • The values of the aggregation fields calculated for the group.

For each distinct value in the non-aggregation fields, the results contain a tuple with all the aggregation fields¶

Edit a Field¶

To edit a field in Output columns you need to:

1. Click the icon next to the field you want to edit.

2. Select the Edit option.

3. Update the name of the field or its expression.

Dialog to edit a field in the Output columns panel¶

Remove a Field¶

If you want to remove a field from the Output columns panel, proceed as follows:

1. Click the icon next to the field you want to remove.

2. Select the Remove option.

If you want to remove several fields, you have the following alternative:

1. Select those fields in Output columns you want to remove.

2. Click the icon in the title.

3. Select the Remove option.

Reorder a Field¶

The order of the fields in the Output columns panel determines how they will appear in the query results. To reorder a field in the output schema, you have to:

1. Click the icon next to the field you want to reorder.

2. Select the Move up or Move down option.

3. Repeat this process until the field is in the correct place.

If you want to reorder several fields, you can proceed as follows:

1. Select those fields in Output columns you want to reorder.

2. Click the icon in the title.

3. Select the Move up or Move down option.

4. Repeat this process until the fields are in the correct place.

As an alternative, you can drag-and-drop a field to its final place in the panel or select those fields you want to reorder and drag-and-drop them all.

Add a Quick Filter¶

A quick filter is a condition that consists of:

1. A field or an expanded field.

2. An operator

3. The values required by the operator, which can range from zero to a list of values.

There are several ways of adding a quick filter:

• Drag-and-drop a field or expanded field from Fields, Relationship fields, Output columns or Order by.

• Click the icon in the title of the Filters panel and select the Add Quick Expression option.

A dialog will appear to define a filter on the selected field.

Dialog to add a quick filter to the Filters panel¶

Take this into account:

• The Field name selector contains the list of fields and expanded fields labeled as <field name> (field type). You can filter this list by typing in the selector.

• The Operator selector shows the list of operators supported by the selected field according to the view configuration.

• The dialog will ask for as many values as the operator requires. Specifically, the in and not in operators require a list of values as parameters. To add a value to the list, write an expression in the editor and click the icon. To remove a value, click the icon.

• The value editor supports all the features we have already seen: highlighting, autocomplete and validation. In addition, if the selected field is of type datetime or interval, the editor will suggest a literal value with the appropriate format.

  See also

  Further information on building datetime and interval values is available in the Virtual DataPort VQL Guide.

Add a Complex Filter¶

In case a quick filter is too restricted for your needs, you can create a complex filter by expressing your condition in VQL. For instance, it may depend on derived fields, use logical operators to combine other conditions, and more.

To add a complex filter you have the following ways:

• Drag-and-drop a derived field from Output columns or Order by.

• Click the icon in the title of the Filters panel and select the Add Expression option.

A dialog will appear to define a complex filter. Just type the condition you need using the rules of VQL.

Dialog to add a complex filter to the Filters panel¶

Add a Filter Using Recommendations¶

You can add filters by using recommendations based on past activity of all the users. Conditions used in queries executed previously in Data Catalog will be recommended to build new queries.

To add filters using general recommendations for a view, you have to:

1. Click the icon in the title of the Filters panel.

2. Select the Recommendations option.

3. Select one of the recommended filters.

4. For quick filters, complete the filter with the required values.

5. For complex filters, replace ? if any by a constant value in Expression.

To add filters using specific recommendations for a view field, you have to:

1. Click the icon of a view field in the Fields panel.

2. Select the Recommended filters option.

3. Select one of the recommended filters.

4. For quick filters, complete the filter with the required values.

5. For complex filters, replace ? if any by a constant value in Expression.

Dialog to add filters using recommendations¶

Another way to add a filter based on recommendations is using the autocomplete feature when you are typing in the filter expression editor.

Dialog to add filters based on recommendations using autocomplete feature¶

Edit a Filter¶

To edit a condition in the Filters panel you need to:

1. Click the icon next to the filter you want to edit.

2. There are two options in the menu to edit the condition:

   • Quick Edit. This option opens a dialog to edit the components of a quick filter: field, operator and values. It is only available for quick filters.

   • Edit Expression. This option opens an editor where you can update your condition using VQL. It is available both for quick and complex filters.

3. Select the option you want and update the filter.

Remove a Filter¶

To remove a filter you can follow the same steps explained before for output columns.

Reorder a Filter¶

To reorder a filter you can follow the same steps explained before for output columns.

Add a Sort¶

You can sort your query results by:

• A field from the Fields panel.

• An expanded field from the Relationship fields panel.

• A derived field from the Output columns panel.

• An aggregation field from the Output columns panel.

To add a sort for your query, you have the following options:

• Drag-and-drop a field from the Fields, Relationship fields or Output columns panels.

• Click the icon in the title of the Order by panel and select the Add option. A dialog will appear with all the fields available in the Fields, Relationship fields or Output columns panels. Select one of them.

Change the Order¶

Next to a field in the Order by panel there is an icon that indicates which order will be applied when sorting by the field:

• Ascending order.

• Descending order.

By default, when you add a field to Order by, the tuples are sorted in ascending order. However, you can change its order by one of the following ways:

• Click the icon next to the field in the Order by panel. It is a toggle button: it will alternate from to , and vice versa.

• Click the icon of the field in the Order by panel. According to your current order, one of the following options will be available: Change to DESC or Change to ASC. Select it.

Remove a Sort¶

To remove a sort you can follow the same steps explained before for output columns.

Reorder a sort¶

The position of the fields in the Order by panel determines the order in which the sorts will be applied. First, the tuples are sorted by the first field, then by the second field, an so on.

To change the order in which the sorts are applied, you can reorder the fields in the Order by panel as explained before for output columns.

Once you have defined your query, you can save it for later, which would be useful in the following scenarios:

• The query is not finished and you want to continue later.

• The query is too complex and you will need to execute it often.

• You want to share the query with other person in your company.

• You want to deploy the query as a view in a Virtual DataPort server.

To save the query, click the Save button in the toolbar. A dialog will appear where you have to give the query a name and, optionally, a description. You will find your saved query in the My queries section.

Dialog to save a query for later¶