Uniqodo Query Language (UQL) documentation
Simple English-like language for writing conditions and rules that must evaluate to either true or
false.
Operators
| Operator | Description | Example |
|---|---|---|
| == | Equal to | basketRoomNight == 3 |
| != | Not equal to | productCategory != “fashion” |
| > | Greater than | basketTotal > 100 |
| < | Less than | basketTotal < 100 |
| >= | Greater than or equal to | basketTotal >= 70 |
| <= | Less than or equal to | quantity <= 2 |
| in | Check value is in a list | "wifi" in basketRoomExtras |
| notIn | Check value is NOT in a list | “kitchen” notIn categories |
| match | Match a string | hotelName match "Croydon" |
| &&, AND | And operator | basketRoomNight >= 3 && customerType == ‘new’ |
| ||, OR | Or operator | (basketTotal >= 70 || quantity <= 2) (basketTotal >= 70 OR quantity <= 2) |
| dateLt | Date less than | (checkInDate dateLt “20-10-2017”) |
| dateGt | Date greater than | checkOutDate dateGt “31-12-2017” |
| dateEq | Date equals | checkOutDate dateEq “31-05-2017” |
Functions
All date functions and operators accept some English representation of dates in place of the actual numeric date string e.g. “yesterday”, "last day of this month", "last day of last month", “tomorrow”, “7 days ago”, “now”, “last Friday”, “next Monday”, “+3 days” (means 3 days from now or in the future), “+1 hour” (means 1 hour from now), “-2 hours” (2 hours ago), “+1 week 2 days 4 hours 2 seconds”.
| Function | Description | Parameters | Examples |
|---|---|---|---|
| dateDiff(date1, date2, unit) | Difference in days between two dates. The underlying logic subtracts date1 from date2 i.e. date2 - date1. The expression evaluates to a positive value if date2 is greater than date1 otherwise returns a negative value. | date1- date string date2- date string unit (optional)- the unit for the returned value i.e. days, hours, minutes or seconds. Defaults to days. | dateDiff(“20-04-2017”, “21-04-2017”) == 1 - this evaluates to true because the result of the expression is 1 dateDiff(“21-04-2017”, “20-04-2017”) == 1 - evaluates to false because the result is -1 f rom (“ 20-04-2017” - “21-04-2017”) dateDiff(“now”, checkInDate) == 7 - Checks whether the variable checkInDate is 7 days from now dateDiff(“21-04-2017 13:00”, “21-04-2017 18:00”, “hours”) == 5 - Evaluates to true |
| dateModify(dateTo Modify, modifier) | Add or remove value from a date | dateToModify- corrrectly formatted date string Modifier- value to add or remove. See the comment about English representation of dates above | dateModify(“24-04-2017”, “-2 days”) - returns “22-04-2017” dateModify(“24-04-2017”, “+3 hours”) - returns “24-04-2017 03:00:00” |
| dateInRange(dateT oCheck, startDate, endDate, startDateModifier, endDateModifier) | Checks whether a date is within a range of dates | dateToCheck - the date to look for within the range startDate- the start of the date range endDate- end of the date range Optional params: startDateModif ier- add days to the start date endDateModifi er- add days to the end date | dateInRange(“23-04-2017”, checkInDate, checkOutDate) dateInRange(checkInDate, “next week”, “last day of this month”) - checkInDate within next week and last day of current month dateInRange(“01-05-2017”, checkInDate, checkOutDate, 0, -1) - adds 0 to start date and -1 to checkOutDate i.e. use a day before the end date as the end of the range |
| dateNotInRange(da teToCheck, startDate, endDate, startDateModifier, endDateModifier) | Checks a date is NOT within the specified start and end dates | same as dateInRange | same as dateInRange |
| consecutiveDays(da ysToCheck, startDate, endDate, startDateModifier, endDateModifier) | Checks two consecutive days are within a set date range | Same as containsDay | consecutiveDays(“Friday,Saturday,Sunday”, checkInDate, checkOutDate) - Checks if the checkInDate and checkOutDate consecutively contains Friday, Saturday and Sunday |
| days(startDate, endDate, startDateModifier, endDateModifier) | Returns an array of days in the date period | startDate- the start of the date range endDate- end of the date range Optional params: startDateModif ier- add days to the start date endDateModifi er- add days to the end date. Use -1 to exclude the endDate in the check | Can be used with the in and notIn operators. “Sunday” in days( checkInDate, checkOutDate) - Checks whether the period between the checkInDate and the checkOutDate variables contain a Sunday “Sunday” notIn days(checkInDate, checkOutDate, 0, -1) - Checks that 'Sunday' is not in the date period checked. Also exclude the checkOutDate from the check. |
| dateLimit(itemCode , startDate, endDate, limits) | Returns a number- the lowest limit for the item code over the date range | startDate- the start of the date range endDate- end of the date range Limits - array populated with limits from the database | dateLimit(hotelCode, checkIn, checkout, limits); |
Advanced Examples
- basketRoomNight >= 3 && ("wifi" in basketRoomExtras || "lateOut" in basketRoomExtras)
- true if basketRoomNight is greater than or equals 3 AND either "lateOut" or "wifi" exists in basketRoomExtras
- basketRoomNight == 5 || hotelName match "Croydon"
- true if basketRoomNight is exactly 5 OR the text "Croydon" exists in the hotelName value
- customerType != "business" && basketRoomCheckIn == "01/02/2017"
- true if customerType is not "business" AND basketRoomCheckIn value is exactly "01/02/2017"
- (productCategory in ["clothing", "kitchen"] && basketTotal > 100.00) || (customerType =="new")
- true if productCategory is either "clothing" or "kitchen" AND basketTotal is more than 100.00. Also evaluates to true if customerType is "new"
- totalNumberOfProducts in ["5", "4", "3"] && customerRegion == "EU"
- (dateInRange(dateModify(“now”, “+1 day”), checkInDate, checkOutDate) && “01-05-2017” dateEq checkInDate) OR (hotelName match "Guildford")
Guidelines
- String values must be quoted, integer values must NOT be quoted e.g. "wifi", "new", 22, 10. Words not quoted in the rule text are treated as variables e.g. productCategory
- Independent conditions must be grouped using parenthesis ()
- Array of values must be enclosed in square brackets [] and items separated by comma e.g. ["breakfast", "GB123", 3]
- Every keyword (operators, functions, values and variables) must be followed by a space before adding the next keyword
- Only supported operators and functions can be used
Promotion & Code Data Items
The list of promotion and code data items that can be used as variables in UQL. These data are
fetched and made available to the UQL parser before the rule is evaluated.
| Parameter | Example Value | Comment |
|---|---|---|
| uqlPromotionStartDate | "2017-05-04 00:00:00" | The promotion start date |
| uqlPromotionEndDate | "2017-05-31 00:00:00" | The promotion end date |
| uqlPromotionTotalRedemption | 900 | The total number of redemptions for a promotion |
| uqlPromotionLastRedemptionDate | "2017-05-09 13:51:54" | The last time a code was redeemed |
| uqlPromotionTotalRevenue | 2,900.00 | Promotion’s sum of order values/transaction amount |
| uqlPromotionTotalDiscount | 230.00 | Total discount issued on the promotion |
| uqlCodeClaimDate | "2017-05-09 13:46:41" | Unique code claim/issue date |
| uqlCodeTotalRevenue | 100.55 | Sum of order values/transaction amount for this code usage |
| uqlCodeTotalDiscount | 200.50 | The amount of discount issued from redeeming this code during the promotion |
| uqlCodeTotalRedemption | 1 | Number of times a code has been redeemed |
| uqlCodeLastRedemptionDate | "2017-05-09 13:51:54" | The last time a code was redeemed |
| uqlCustomIdentifier | E.g. an email address | The uploaded merchant’s validation custom data identifier column |
| uqlCustomP1...uqlCustomP10 | Any value, including dates | The uploaded merchant’s validation custom data items 1 up 10 |
Updated over 1 year ago