Properties - Validation
Vizlib Writeback Table can help you drive improvements in your data quality by offering validation conditions which specify rules to check the data entered in a column or field. If the data does not meet the conditions, an error is displayed. Validation can relate to a particular type of data (e.g. numeric values only), or a recognised format (e.g. email address).
Validation ensures the information you collect and hold in your system follows your schema, and fits your requirements, bringing a wide range of benefits to your organisation. As an example, accurate sales data can be used to produce more specialised and focused sales reporting and can drive improved targets in customer retention and acquisition.
This topic contains the following sections:
Validation
To start managing your validation settings in your writeback table, click the column you want to work with, then click Validation in the property panel. By default, no validation is applied. Required if allows you to enter a value or expression to evaluate whether validation is required for an individual cell (a column in a specific row). Required if evaluation level determines whether the cell is evaluated in the context of the Row or the Table. Figure 1 shows settings where a Row-level evaluation is in place, and the value for Required if is set to 1, which enables the validation condition. If you select Use Variable in Calculation, the calculation property is evaluated before it's used in the Required if condition.
Note: Using Required if for large datasets with Row level evaluation can affect performance as each row requiring validation will be evaluated individually in the Qlik Sense Engine.
Figure 1: Validation
The example in Figure 2 uses table-level required if in column Name and row-level in column Details with an expression 'Column(DetaulsRequired)=1' and is only required when the Details Required column is checked.
Figure 2: Validation
The Validation drop-down lists the validation types you can select. The example in Figure 3 uses Email.
-
Alphanumeric - value must contain letters and numbers.
-
No Validation - value not restricted by any conditions.
-
Email - value must be a valid email address. Phone - value must be a valid phone number.
-
Alpha - value must contain letters only.
-
Regex - value must contain a specified regular expression (e.g. password format).
-
Expression - value must match a specified Qlik Sense expression.
You can enter text for the Error Message, and the Required label (only visible for evaluation at row level) adds a short instruction in the column header. If you need to change the color of the error message or required label, you can find all validation color settings in the custom styling section for the column in the property panel.
Useful RegExes
Regex is very powerful but can be hard to learn. Below you see some good examples of useful regexes.
| Regex | Description |
|---|---|
| ^\d{4}-([0]\d|1[0-2])-([0-2]\d|3[01])$ | ISO string date YYYY-MM-DD e.g. 2022-12-31 |
| ^(0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$ | Time 24 hours HH:mm e.g. 21:15 |
| ^((1[0-2]|0?[1-9]):([0-5][0-9]) ?([AP][M]))$ | Time AM/PM HH:mm AM/PM e.g. 09:15 PM |
| (?=(.*[0-9]))((?=.*[A-Za-z0-9])(?=.*[A-Z])(?=.*[a-z]))^.{8,}$ | Password simple, with at least 1 lower case, 1 uppercase and 1 number. It needs to be at least 8 characters long. |
| (?=(.*[0-9]))(?=.*[\!@#$%^&*()\\[\]{}\-_+=~`|:;"'<>,./?])(?=.*[a-z])(?=(.*[A-Z]))(?=(.*)).{8,} | Password complex, with at least 1 lower case, 1 uppercase, 1 number and 1 special character. It needs to be at least 8 characters long. |
| https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#()?&//=]*) | URL with https protocol e.g https://home.vizlib.com |
Note: To make the Regex work, you need to enclose it in quotation marks.
Example
Figure 3 shows an example of an Email validation being applied in Vizlib Writeback Table. Once the Required if setting is enabled to 1, the Email Address field is highlighted in red and an Error Message is entered, along with a symbol for Required if. The red highlight disappears when a valid email address is entered.
Figure 3: Validation Selection