I am always learning something new when it comes to Qlik products. This week, while working with a colleague, I learned about the sheet action “select values matching search criteria” that I had never used before, so I decided to blog about it. I have used sheet actions in the past to clear selections or to select a bookmark when navigating to a sheet, but this sheet action performs a search to make selections. My colleague needed to make a series of selections to include and exclude data based on specific criteria. Using this sheet action, he was able to execute a complex expression to make selections on a sheet saving the users time when they navigate to that sheet.
Up to five actions can be added and they will run in the order they are listed. Sheet actions can be added from the Properties panel of a sheet. In this example, I have 3 actions.
Quantity > 100 actionBabywear action
In the first action, I am selecting quantities greater than 100. Notice in the Quantity > 100 action that single quotes are required when using an expression for the search criteria. It is possible for an action to deselect a previous action. For example, the first action selects 110, 120, 130 and 150 from the Quantity field. Then, in the second action, Babywear is selected from the CategoryName field, deselecting 110, 130 and 150 from the Quantity field. The last action selects 2021 from the Year field which deselects Babywear. So once all the actions are performed, these are the selections made.
Here is a list of all the sheet actions that are available:
Apply bookmark – Apply an existing bookmark when navigating to a sheet.
Clear all selections – Clear all selections when navigating to a sheet.
Clear selections in other fields – Clear all selections from all fields except the one specified.
Move forwards in your selections – Move one step forward in selection history.
Move backwards in your selections – Move one step backwards in your selection history.
Clear selections in field – Clear all selections from a specified field.
Lock all selections – Lock all selections in the app.
Unlock all selections – Unlock all selection in the app.
Unlock a specific field – Unlock selections in a specified field.
Select all values in a field – Select all values in a specified field.
Select values in a field – Select value(s) in a specified field.
Select values matching search criteria – Select values that match specified search criteria.
Select alternatives – Select alternative values in a specified field.
Select excluded – Selected excluded values in a specified field.
Select possible values in a field – Select possible values in a specified field.
Toggle field selection – Set a button to toggle between current selections and a selection that adds selections defined by a search string.
Set variable value – Assign a value to a variable.
Most of these are very straightforward but you can find more details on sheet actions in Qlik Help. Some important things to note:
You cannot set an action based on the results of another action because they are all evaluated before they are performed.
Sheet actions will not be triggered if the sheet is used in a mashup or an embedded sheet.
Some of these actions give you the option to override locked selections. See Qlik Help for list.
You cannot use fields with date, timestamp, or money data type.
With the “select values matching search criteria” sheet action, you can use an expression or simply a value to search for and make selections in a specified field. This can be useful if you have a sheet in your app designed to perform a specific analysis that requires some selections to be made to begin. Using an action makes it easier for the user to get started and leaves little room for error by forgetting to make needed selections. I love that I am always learning something new with Qlik. Try out sheet actions in your app to automatically apply selections.