3.4.2.1 Data Validation Tests - JavaScript mode

Abbreviations, used in manual:

QG - Quality Gates

DV - Data Validation

JS - JavaScript

CC - Calculated Column

This guide contains a detailed description of all quilliup embedded DV functions available in JS mode.

JS mode is applicable for all Data Set types, although, when using RDBMS Data Sets, it is highly recommended to use SQL mode!

Rules:

There are 2 ways to create a Rule:

1. If same Rule should be used in more than one Test => create external Rule (can be used in different Tests many times) in the Library (possible only for the Rules of JS type).

In QG module in the left side menu open My Projects section -> choose a specific Project, Library section -> choose Rules section:

Operations, available in the Rule's grid:

Bulk Operations - allows to mark a few Rules at a time and to Delete or Copy them together.

Deploy/Deploy All - allows to Deploy selected Rules/all the Rules in the page from one environment to another.

A Rule(s) can also be Edited/Copied/Deleted by clicking relevant icons in each row.

2. In QG module, in order to create internal Rule (can be used in one specific Test only), in the left side menu open My Projects section -> choose a specific Project -> select Tests -> Add Test -> select Data Validation Test type -> Add Rule:

Adding a new JS rule:

Import From Library - allows to choose an existing external Rule from the list of Rules available in current Project. 

Name - you can insert a name manually or to choose "Auto generate name".

Description - short Rule description.

Severity - define Rule's severity (default value is "Normal").

Tags - choose relevant Tag(s) if needed.

Rule Formula - Rule's body.

i sign- shows shortcuts:

</>sign - switches to free JS code

Calculated Columns - list of previously created for this Rule calculations. *Please refer to detailed explanation below

Folder sign - list of functions of different types (such as Date & Time, Logical, ...) can be used.

**Please refer to detailed explanation below

Table sign- shows the list of Data Set's columns.

{:} sign- shows the list of available variables.

Each column's name should be surrounded with square brackets. For example: [<column>]

In order to use variable in Rule's Formula, type @ sign and list of available Variables will be shown:

In order to be able to use Calculated Columns (should be previously created), type # sign and list of available Calculated Columns will be shown, for example:

* Calculated Columns:

For example, you can create a Calculated Column (A) that will be used in the formula (B) and will be kept in the results along with the existing columns. A Calculated Column will be created and will be available in the formula section under the column's list. In the formula, we’ll define what is the condition for the calculations we did in the first step.

(A) 

It is possible to create a New column (with specific formula/calculation) in order to make calculations simpler.

Click "Calculated Columns"-> "Add New":

Calculated Column form will open:

Name - CC name.

Type - choose the type of the value that will be returned from this CC (String, Boolean, Number, Decimal, Date etc.).

</> area - formula for the CC. A function from the list of predefined functions can be used (sections Date & Time, Logical, Math, Text). 

Existing Data Set's columns can be used in formula (click  the table sign) or same as variables (the{;} sign) for the new CC. 

Please make sure that your formula (B) is set properly by clicking "Validate" button.

CC can be saved by clicking "Save" button.

It will appear in the columns list with "#" to differentiate it from the Data Set's columns:

This way CCs can be used in Rule's formula.

Threshold -allows to define a specific threshold for the Rule.

** Explanation of the different built-in functions:

Date Functions:

• DATEFORMAT - returns date string in selected format out of a supported list of formats that needs to be specified ('DD/MM/YYYY','MM/DD/YYYY','DD-MM-YYYY','MM-DD-YYYY'). Example: DATEFORMAT('2016-12-31 23:02:06','MM/DD/YYYY').
• SIMPLEDATE - returns date object in milliseconds. Specify the format according to the following: 'DMY' (Day, Month, Year), 'YDM', 'YMD'. Example: SIMPLEDATE('31/12/2016 20:15', '/', 'DMY').
• TIME - returns time string in selected format out of a supported list of formats that needs to be specified ('HH:MM:SS','HH:MM','HH'). Example: TIME('12/31/2016 23:02:06', 'HH:MM:SS').

• DAY - returns the day of a month, a number from 1 to 31. There are generally 4 types of JavaScript date formats:

  1. ISO Dates (for example, 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS').
  2. Short Dates (for example, 'MM/DD/YYYY','YYYY/MM/DD') *Notice: month should be specified before day in all Short Dates and ISO Dates formats.
  3. Long Dates (for example, 'January 31 2015', 'Jan 31 2015').
  4. Full Format (for example, 'Sat Jan 31 2015 09:56:24 GMT+0100 (W. Europe Standard Time)').

  Example: DAY('2015/12/31')

• DAYOFWEEK - returns the day of the week from number 1 (Sunday) through 7 (Saturday). There are generally 4 types of JavaScript date formats:

  1. ISO Dates (for example, 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS').
  2. Short Dates (for example, 'MM/DD/YYYY','YYYY/MM/DD') *Notice: month should be specified before day in all Short Dates and ISO Dates formats.
  3. Long Dates (for example, 'January 31 2015', 'Jan 31 2015').
  4. Full Format (for example, 'Sat Jan 31 2015 09:56:24 GMT+0100 (W. Europe Standard Time)').

  Example: DAYOFWEEK('2015/12/31')

• EOMONTH - returns the date object of the last day of the month before or after a specified number of months. There are generally 4 types of JavaScript date formats:

  1. ISO Dates (for example, 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS').
  2. Short Dates (for example, 'MM/DD/YYYY','YYYY/MM/DD') *Notice: month should be specified before day in all Short Dates and ISO Dates formats.
  3. Long Dates (for example, 'January 31 2015', 'Jan 31 2015').
  4. Full Format (for example, 'Sat Jan 31 2015 09:56:24 GMT+0100 (W. Europe Standard Time)').

  Example: EOMONTH('2015/12/31',-3)

• MONTH - returns the month (from 1-12). There are generally 4 types of JavaScript date formats:

  1. ISO Dates (for example 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS').
  2. Short Dates (for example 'MM/DD/YYYY','YYYY/MM/DD') *Notice: month should be specified before day in all Short Dates and ISO Dates formats.
  3. Long Dates (for example 'January 31 2015', 'Jan 31 2015').
  4. Full Format (for example 'Sat Jan 31 2015 09:56:24 GMT+0100 (W. Europe Standard Time)').

  example: MONTH('2015/12/31').

• QUARTER - returns the quarter of a date. There are generally 4 types of JavaScript date formats:

  1. ISO Dates (for example, 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS').
  2. Short Dates (for example, 'MM/DD/YYYY','YYYY/MM/DD') *Notice: month should be specified before day in all Short Dates and ISO Dates formats.
  3. Long Dates (for example, 'January 31 2015', 'Jan 31 2015').
  4. Full Format (for example, 'Sat Jan 31 2015 09:56:24 GMT+0100 (W. Europe Standard Time)')

  example: QUARTER('2015/12/31').

• YEAR - returns the year of a date (type number). There are generally 4 types of JavaScript date formats:

  1. ISO Dates (for example 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS')
  2. Short Dates (for example 'MM/DD/YYYY','YYYY/MM/DD') *Notice: month should be specified before day in all Short Dates and ISO Dates formats.
  3. Long Dates (for example 'January 31 2015', 'Jan 31 2015')
  4. Full Format (for example 'Sat Jan 31 2015 09:56:24 GMT+0100 (W. Europe Standard Time)')

  example: YEAR('12/31/2015').

• HOUR - returns the hour as a number from 1 (12:00 AM) to 23 (11:00 PM). example: HOUR('2015/12/31 17:01:01').
• MINUTE - returns the minute as a number between 0 and 59. example: MINUTE('2015/12/31 17:55:01').
• SECOND - returns the second as a number between 0 and 59. example: SECOND('12/31/2015 23:02:06').
• TODAY - returns today's date formatted as date. example: TODAY().
• NULLDATE - returns null. Example: NULLDATE().

Logical Functions:

• AND - receives an input of a logical rule (2 and more). Returns true if all values are true. Example: AND(1,1,1,0).
• OR - receives an input of a logical rule (2 and more). Returns true if any of the values is true. Example: OR(true, false, false).
• IF - conditional statements are used to perform different actions based on different conditions. Example: IF(1+1==2,'T','F').
• PATTERN - receives a pattern to be matched. Numeric values are replaced with #, characters are replaced with X, special chars are not replaced (e.g, AA-123 will be matched to a pattern XX-###). Returns TRUE/FALSE. Example: PATTERN('AA-123','XX-###')
• ISEMPTY - returns true if empty. Example: ISEMPTY('').
• ISNULL - returns true if null. Example: ISNULL('NULL').
• NOTNULL - returns false if null. Example: NOTNULL(5).

Math functions:

• ABS - returns the absolute value of a number. Example: ABS(-7.25)
• ROUND - rounds the number according to a specified number of digits. Example: ROUND(23.2645345,1).
• ROUNDUP - rounds the number up according to a specified number of digits. Example: ROUNDUP(2.305,0).
• ROUNDDOWN - rounds the number down according to a specified number of digits. Example: ROUNDDOWN(2.299,1).
• POW - returns the value of number x to the power of number y. Example: POW(4,3).
• SQRT - returns the square root of number. Example: SQRT(9).
• RANDOM - returns a random number between 0 and 1. Example: RANDOM().
• MAX - returns the largest value (number, date or text) in a list of values. *Notice: same format should be used in the input values. Example: MAX(5,10).
• MIN- Returns the smallest value (number, date or text) in a list of values. Notice: You need to use the same format in the input values. example: MIN(5,10).
• NULLNUMBER - returns null. Example: NULLNUMBER().
• ISNUMBER - check if a value is a number. Function will return TRUE when value is numeric and FALSE when not. Example: ISNUMBER(2.5).

Text Functions:

• CONCAT - joins several text strings into a single text string. If function receives no arguments, it returns empty result. If one of the arguments is NULL, it will be ignored and all other arguments will be concatenated without any changes. Always returns String. Example: CONCAT('hello','world').
• FIND - returns the starting position of first found text string within another text string. If string not found, returns -1. You can use 'icase' to ignore case-insensitive enforcement. Example: FIND('Mr. Blue has a blue house','blue'); FIND('Mr. Blue has a blue house','BLUE','icase').
• LEFT - extracts a specified number of characters from the beginning of a text string. If number of characters isn't specified - all the text string is returned. Example: LEFT('Hello world',5).
• RIGHT - extracts a specified number of characters from the end of a text string. Example: RIGHT('Hello world',3).
• LOWER - converts all characters to lower case. Example: LOWER('Hello World!').
• REPLACE - replaces existing text with a new text in a text string. You can use 'icase' to ignore case-insensitive enforcement. If string not found , returns the text without changes. Example: REPLACE('Mr Blue has a blue house and a blue car','blue','red'); REPLACE('Mr Blue has a BLUE house and a blue car','blue','red','icase');
• TRIM - removes whitespace from both sides of a string. Example: TRIM(' Hello World! ')
• UPPER - converts all characters to upper case. Example: UPPER('Hello World!').
• ENDSWITH - checks whether a string ends with specified string/characters. Returns True or False. Example: ENDSWITH('Hello world, welcome to the universe.','universe.').
• STARTSWITH - checks whether a string begins with specified characters. Returns True or False. Example: STARTSWITH('Hello world, welcome to the universe.','Hello').
• MATCH - gets text and pattern, searches the first occurrence of the pattern in the text and returns it. If no match is found, it returns null. User must validate his regex in JavaScript. Example: MATCH('a-1 a-2 a-3','[a-zA-Z]-\d'); MATCH("[e-mail]",'^([a-zA-Z0-9_.-])+\@(([a-zA-Z0-9-])+.)+([a-zA-Z0-9]{2,4})+$').
• NULLSTRING - returns empty string. Example: NULLSTRING().
• IFNULL - If receives null - returns chosen ret_val, else returns received value (val). Example: IFNULL(null, 'null received').
• ESCAPE - the function handles these special characters $,^,*,(,),+,[,|. Example: ESCAPE('B+4').
• LEN - returns the number of characters in a text string. Example: LEN('Hello World!').

Note: It is possible to add more customized functions. Kindly use the following manual for a full explanation: Functions 

 

Examples for combination of Rules

Example 1:

The table:
Field 1: tableID (int) - table key
Field 2: dependence (boolean) - simulates a dependence field and will use in the rule that we will create
Field 3: A (int) - any number value
Field 4: B (int) - any number value
Field 5: C (int) - any number value
Field 6: myString (string)

•  

  • Rule 1(shows how to use OR and AND functions together):

If dependence field = true -> The sum of A + B + C should be greater than 0
Rule Formula: OR ( AND ( IF([A] + [B] + [C] > 0, 1, 0) , IF([dependence] ,1,0) ) , IF(![dependence] ,1,0) )

Success rows examples:

Failed rows examples:

•  

  • Rule 2 (shows how to use IF function):

The field A should be greater than field B.
Rule Formula: IF ( [A] > [B] , 1 , 0 )
First tab is the condition, second tab is value if true (0 or 1), Third tab is value if false (0 or 1)
A and B fields can be any comparable type (like date or float), but have to be with same type

Success rows examples:

Failed rows examples:

•  

  • Rule 3:

The field C should be greater than 0 and lower than variable @constant10; value.
Rule Formula: AND([C]<@constant10; , [C]>0)
The variable is defined on the project level. You can use any variables from your project.

In this example the variable @constant10; has the constant value 10.

Success rows examples:

Failed rows examples:

•  

  • Rule 4 (shows how to use OR function and != argument):

At least one of the fields A, B, or C should be different from 0.
Rule Formula: OR ( [A]!=0 , [B]!=0 , [C]!=0 ) 

Success rows examples:

Failed rows examples:

•  

  • Rule 5 (show how to use AND function and != argument):

The field myString should be different from 0, null or ' '.
Rule Formula: AND ( [myString]!=null , [myString]!=0 , [myString]!= ' ')

You can add any condition that you need on your string.

Success rows examples:

Failed rows examples:

•  

  • Rule 6 (shows how to check any condition only if other condition is true):

If the field dependence = true => the field myString should be different from 0, null or ' '.
Rule Formula: OR ( AND ( [myString]!=null , [myString]!=0 , [myString]!= ' ' , [dependence]) , [dependence]!=1 )

Success rows examples:

Failed rows examples: