Formulas in placeholders

Placeholders are used in input fields of blocks, in the Data automation editor, to reference outputs of other blocks. Example of a placeholder:

You can click the view icon above the input field (on the right hand side) to switch from normal view to advanced view. Example of the same placeholder in advanced view:

For example, the placeholder { $.getContact.name } references the field name from the output of the block named Get Contact.

Using formulas

It is possible to use formulas inside placeholders, to manipulate the data. The formula will be applied on the result of the placeholder.

The formula name is put in front of the path of the placeholder, inside the curly brackets. Some formulas have extra parameters, they are added behind the path with a comma.

Example of a placeholder: { $.path }

Example of the same placeholder with a formula added:

{ formula-name: $.path, parameter1, parameter2 }

Combining formulas

You can combine more than one formula. Add curly brackets around the inner formula, and add an outer formula around it. Example:

{ outer-formula: { inner-formula: $.path, inner-parameters }, outer-parameters }

Text Functions

The following functions allow you to dynamically modify text.

Uppercase and Lowercase

Changes a text to all capital characters (uppercase) or all lower case.

Usage: { uppercase:$.jsonpath } Usage: { lowercase:$.jsonpath }

Example: { uppercase:$.getCompany.name } Example: { lowercase:$.getCompany.name }

Capitalize name

Add capitals to all words in a name, but only when the full name is all small caps or all capitalized.

Usage: { capitalizeName: $.path }

Example: john doe will be converted to John Doe JOHN DOE will be converted to John Doe jan van den Broeck will not be changed (since it already has one capital. This is to avoid changing intended lower case in words such as de or van).

Trim

Trims a given character from the left and right of a string. The character to trim (remove) is optional. If it is not set, the text will be trimmed on spaces and line breaks.

Usage: { trim:$.jsonpath, "character_to_remove" }

Example: { trim:$.getCompany.url, "/" }

You can also use formulas ltrim and rtrim to only trim on the left or right hand side of a text.

Regex parse

Parses a text (string) from a given text based on a regular expression (regex). Only the first match of the regex will be returned. If you want to return all the matches, use regexparseall instead.

Usage: { regexparse:$.jsonpath, "regex" }

Example to parse the part after .com in a URL: { regexparse:$.getCompany.url, "\.com(.*)" }

Example to parse only the first name or last name from a full name (assuming format Firstname Lastname):

Get first name: { regexparse:$.fullname, '^([^\s]*)' } Get last name: { regexparse:$.fullname, '^[^\s]*\s(.*)' }

The first function will remove everything from $.fullname starting at the first space found. The second function will remove everything up to the first space.

See also: Useful regular expressions

Regex parse all

Parses a text (string) from a given text based on a regular expression (regex) and returns a single list (array) with all matches. This formula supports only one capturing group in the regex. If you only want the first match, use regexparse instead.

Usage: { regexparseall:$.jsonpath, "regex" }

See also: Useful regular expressions

Regex replace

Replaces a text (string) in another text based on a regular expression (regex). Each match of the regex will be replaced. The new text can have a placeholder $1 to insert the text that matches a capture group from the regex such as e.g. "(.*)".

Usage: { regexreplace:$.jsonpath, "regex", "new text" }

Example that replaces .be in .com in a URL: { regexreplace:$.getCompany.URL, "(.*)be", "$1com" }

Example that removes special characters from a text (everything that is not a-z or a number): { regexReplace: $.getCompany.URL, "[^\w\s\-_]", "" }

See also: useful regular expressions.

Replace

Replaces a text (string) in another text.

Usage: { replace:$.jsonpath, "old text", "new text" }

Example: { replace:$.getContact.name, "Mr", "Mister" }

Remove

Removes a text (string) from another text.

Usage: { remove:$.jsonpath, "wrong text" }

Example: { remove:$.getContact.name, "Mr" }

Substring

Returns part of a text (string) based on a fixed start position and length.

Usage: { substr:$.jsonpath, startpos, length }

Example: { substr:$.getContact.name, 5, 10 }

In the above example, the start position is 5 and the length of the text to return is 10 characters. Note that the first position of a text is 0.

String Position

Returns the position of the first occurrence a text in another text (first position is 0).

Usage: { strpos:$.jsonpath, "text to find" }

The following variants are also available:

strrpos: reverse search (right to left, finds the last occurrence of a text in another text) stripos: case-insensitive search strripos: case-insensitive reverse search

Number, Boolean, Text, List, Object

Apply casting on data, which means that you specifically convert an input or output to e.g. a boolean value (True/False). This is useful when using e.g. custom fields in an update, where Qlik Application Automation for OEM does not know the type of the field. When you use the value true (typed in as text), this may cause an error because it is sent as the word true and not the boolean value True. This can be solved by using { boolean:true } instead.

Usage: { boolean:$path }

Example: { boolean:true }

Text Length

Returns the length of a text (number of characters including spaces, punctuation etc.).

Usage: { text_length: $.path }

toAscii

This formula will convert a text with special characters to a plain text with ASCII characters only. This is useful when sending data to a platform that does not handle special characters (e.g. UTF-8) well.

Usage: { toAscii: $.path }

Example: When $.path value is e.g. Gérard the result will be Gerard.

Explode

Converts a text with e.g. comma-separated items into a list.

Usage: { explode:$.someJsonPath } or { explode:$.someJsonPath, "character(s)" }

Example to explode on commas (default): { explode:$.contactlist }

Example to explode on spaces: { explode:$.contactlist, " " }

Random Text

Generates a random text, useful to generate e.g. personal discount codes in a webshop.

Usage: { randomText:5 }

In the above example, 5 is the length. This parameter is optional. Result will be e.g. fkelz.

Line break

Inserts a line break (new line) in a string or text, e.g. for formatting in an email.

Usage: {linebreak}

Convert Functions

The following functions deal with converting data types.

Number, Boolean, Text, List, Object

Apply casting on data, which means that you specifically convert an input or output to e.g. a boolean value (True/False). This is useful when using for example custom fields in an update, where Qlik Application Automation for OEM does not know the type of the field. When you use the value true (typed in as text), this may cause an error because it is sent as the word true and not the boolean value True, this can be solved by using { boolean:true } instead.

Usage: { boolean:$path }

Example: { boolean:true }

ISO To Country and Country To ISO

Convert 2-letter country codes (ISO 3166 Alhpa-2 codes) to the full country name in English, or vice versa.

Usage: { isotocountry: $.path, "language" } { countrytoiso: $.path, "language" }

The parameter language is optional. If used it has to be a 2-letter language code (EN, DE, FR...).

Examples:

{ isotocountry: $.path } will result in France when the value of $.path is equal to FR or fr

{ isotocountry: $.path, "DE" } will result in Frankreich when the value of $.path is equal to FR or fr

{ countrytoiso: $.path } will result in FR when the value of $.path is equal to France

Domain

Extract a domain name from a full website URL. Useful when cleaning data for data enrichment, where you look up company data using a data provider.

Usage: { domain:$.website }

The result will be google.com when $.website is e.g. https://www.google.com/about.

Explode

Converts a text with e.g. comma-separated items into a list.

Usage: { explode:$.someJsonPath } or { explode:$.someJsonPath, "character(s)" }

Example to explode on commas (default): { explode:$.contactlist }

Example to explode on spaces: { explode:$.contactlist, " " }

National Phone

Normalize a phone number into a national format (meaning without the international access code).

Usage: { nationalphone:$.tel }

For example, a phone number +32 (0)498667788 will be formatted into 498667788 (note that the leading zero is not added or included).

Phone

Normalize a phone number into an international format.

Usage: { phone:$.tel, 'countrycode' }

The second parameter is an optional country code, it is used to normalize a phone number when its international access code (e.g. +44) is missing.

Example: { phone:$.tel, 'uk' } or { phone:$.tel }

For example a phone number +32 (0)498667788 will be formatted into +32 498 66 77 88.

To Phone

Tries to convert a given text to a valid phone number. It will make the text empty otherwise.

Usage: { toPhone: $.path }

Example:

{ toPhone: $.path } when value of $.path is abc this will result in "", and when value of $.path is phone: 555-667788 this will result in 555-667788.

Number, Boolean, Text, List, Object

Apply casting on data, which means that you specifically convert an input or output to e.g. a boolean value (True/False). This is useful when using for example custom fields in an update, where Qlik Application Automation for OEM does not know the type of the field. When you use the value true (typed in as text), this may cause an error because it is sent as the word true and not the boolean value True, this can be solved by using { boolean:true } instead.

Usage: { boolean:$path }

Example: { boolean:true }

Object

This formula turns a text that contains JSON data into an actual object, so you can e.g. assign it to a variable of type object or list.

Usage: { object: $.path }

Example: When $.path value is e.g. [ "one", "two", "three" ], the result will be a list with 3 items.

Json

This formula turns an object into a JSON text, so you can e.g. insert it in a text field (varchar) in a database.

Usage: { json: $.path }

Example: When $.path value is e.g. a list with three items, the result will be a text such as '[ one, two, three ]'.

See also: how to Compare dates

Date Functions

The following functions deal with the date.

Convert timezone

Converts a date time into a different time zone.

Usase: {converttimezone: 'datetime', 'source format', 'source timezone', 'destination format', 'destination timezone'}

Examples: {converttimezone: '2020-01-27T17:55:38', 'Y-m-d\TH:i:s', 'Europe/Paris', 'Y-m-d\TH:i:s', 'UTC'} {converttimezone: '2020-01-27 17:55:38', 'Y-m-d H:i:s', 'Europe/Paris', 'Y-m-d\TH:i:s', 'America/New_York'}

See also: how to Compare dates

Dates and timestamps

Returns a date and/or time for a given timestamp (e.g. now) or converts a given date time from any input format into a new format, for example to convert 3/10/2020 14:00:00 to 2020-03-10T14:00:00Z.

Usage: { date: "timestamp or date time", "new format", "input format (optional)" }

Note that the third parameter input format is optional.

Example to get current date in format 20-03-18: { date: "now", "d-m-y" }

Example to get the date of yesterday in format 19-03-18: { date: "now - 1 day", "d-m-y" }

Example to get current date in ISO-8601 format, e.g. 2018-08-20T12:35:24+0000: { date: "now", "Y-m-d\TH:i:sO" }

Example to get a timestamp (epoch in seconds) for today plus 5 days, e.g. 1534232971: { date: "now + 5 days", "U" }

Example to get the epoch timestamps from the start and end of Yesterday: {date: 'now - 1 day midnight', 'U'} {date: "now midnight", "U"}`

Example to convert a unix timestamp (epoch in seconds) to a readable date: { date: $.epoch, "Y-m-d", "U" }

Example to convert a unix timestamp which is in milliseconds to a readable date (we divide the timestamp by 1000 first and round it): {date: "{round:{divide: {$.epoch_milliseconds}, 1000 }} ", 'Y-m-d', 'U'}

List Functions

The following functions deal with lists.

Count

Count the number of items in a list.

Example: { count:$.getContacts }

Result: e.g. 10

Get keys

Get the keys (fieldnames) of an object.

Usage: { getkeys:$object }

Example: { getkeys:$.getContact }

For example if the result of $.getContact is:

{
  "name" : "John",
  "email" : "john@acme.com"
}

Then the above formula will return ["name", "email"].

Implode

Converts a list of items into one text, separated with a comma or another character.

Usage: { implode:$.someJsonPath } or { implode:$.someJsonPath, "character(s)" }

Examples:

• implode using commas (default): { implode:$.contacts[*].name }
• implode using a comma and a space: { implode:$.contacts[*].name, ", " }
• implode using line break: { implode:$.contacts[*].name, "{linebreak}" }

Sort

Sorts a list ascending.

Usage: { sort: $.list, "keyname" }

The parameter keyname is optional and should be used for lists of objects. The objects in the list will be sorted based on the value in this key. For example, use keyname id when you have a list of objects of the form { id: 123, name: John }.

Note that this formula applies a natural sort (not an alphanumeric sort). This means that the result will be 1, 2, 3, 10 and not 1, 10, 2, 3. The list A5, B3, C2 will be sorted to C2, B3, A5. Use custom code if you want to apply a different type of sorting.

Example for a list of objects with field id: { sort: $.list, "id" }

Example for a list of text or numbers: { sort: $.list }

Sortdesc

Sorts a list descending.

Usage: { sortdesc: $.list, "keyname" }

The usage is identical to the above sort formula.

Math Functions

The following functions deal with math.

Add, Substract, Multiply, Divide, Mod

Apply basic algebra.

Usage: { add: $.jsonpath1, $jsonpath2 } Usage: { substract: $.jsonpath1, $jsonpath2 } Usage: { multiply: $.jsonpath1, $jsonpath2 } Usage: { divide: $.jsonpath1, $jsonpath2 } Usage: { mod: $.jsonpath1, integer } (modulo)

Round

Returns the rounded number of a decimal number.

Example: { round:$.price }

Result: if $.price is equal to 10.30 the result is 10.

Example: { round:$.price, 2 }

Result: if $.price is equal to 10.336 the result is 10.34.

Min and Max

Returns the min or max from a list of items.

Example: { max:$.list[*].id }

Result: e.g. 10

Random Number

Generates a random number, useful to generate e.g. personal discount codes in a webshop.

Usage: { randomNumber:5 }

In the above example, 5 is the length. This parameter is optional. Result will be e.g. 65778.

Formula guid

Generates a random guid, e.g. 74ba8b5d-69bd-428c-ae9b-09b9c3fcbbac.

Usage: { guid }

File functions

The following functions deal with files and file types.

Base64Encode

Used to convert binary file content (using the Open file block) to a base64 encoded string.

Usage: { $.base64encode: { $.readDataFromFile } }

Other Functions

The following functions cover a variety of use cases.

If

Applies a condition on a value and returns one value if the condition is true, and another value if the condition is false.

Usage: { if: $.path1 operator operand, $.path2, 'elsevalue' }

In the above usage, operand can be a jsonpath $.path or a text (e.g. string) or numeric (e.g. 123).

Example: { if: $.path1 = USA, $.path1, unknown country } –> this will replace the value of $.path1 with unknown country if it is not equal to USA.

Following operators are available:

=       (equal)
==      (equal)
!=      (not equal)
<>      (not equal)
<       (smaller than)
>       (greater than)
<=      (smaller than or equal to)
>=      (greater than or equal to)
empty
notEmpty
contain
doesntContain
inList
notInList
isText
isNotText
isNumber
isNotNumber
isObject
isNotObject
isList
isNotList
isEmail
isNotEmail
isPhone
isNotPhone
isTrue
isFalse

These operators operate similar to conditions used in Condition blocks. See Conditions for more information.

Map

Replaces a set of values with a different set of values.

Usage: { map: $.path, objectvariable, 'default' }

In the above usage, objectvariable can be e.g. {'key1':'replace1', 'key2':'replace2'}

Example: { map: $.path, {'UK':'England', 'USA':'America'}, 'default' } –> this will replace UK with England etc. and the result will be default if the value of $.path was not found in the list.

Examples with different notation:

map: $path, 'Belgium -> BE, France -> FR, ...', 'default'
map: $path, {"Belgium" : "BE", "France" : "FR", ...}, 'default'
map: $path, [{"Belgium" : "BE"}, {"France" : "FR"}, ...], 'default'

Remove Empty Properties

Removes the keys from an object where the value is null or an empty string or a string containing only spaces.

Usage: { removeEmptyProperties:$object }

Example $.object:

{
  "name" : "",
  "address" : null,
  "email" : "john@acme.com",
  "remarks" : "    ",
}

The result of applying removeEmptyProperties to $.object is:

{
  "email" : "john@acme.com"
}

This formula can be used to avoid sending empty key/values to an endpoint, which would overwrite existing values. This scenario can happen when you build an object based on source data and some fields are missing in the source data. In that case you do not want to send e.g. name: null to a destination, this would make the name empty.

Formula if not in list

Checks if a value appears in a given list. If not, the value is replaced with a given value.

Usage: { ifNotInlist: $.path, list, default value }

Example: { ifNotInlist: $.path, 'item1,item2,item3', null } –> this will replace the value of $.path with null if it is not equal to item1, item2 or item3.

Examples using different notation: ifNotInlist: $.path, ['FR','NL','BE'], default value ifNotInlist: $.path, $.listvariable, default value

Formula if greater than

Checks if a value is greater than a given value. If so, the value is replaced with a given default value.

Usage: { ifGreaterThan: $.path, value, default value }

Example: { ifGreaterThan: $.path, 100, 50 } –> this will replace the value of $.path with 50 if its value is greater than 100.

Parse Label / Values

Used to parse a text with lines in the format label: value, typically an email with a form submission. Use e.g. the IMAP connector to read emails from form submissions, and use this formule on the plain text of the email body, to get the structured data from the form submission:

Example text:

name: John Doe
email: john@doe.com
question: Hi,
I would like to receive more info.

Usage: { parseLabelValues:$.getMail.text }

The result will be an object with keys name, email and question and their corresponding values. Note that key question will have a value with a line break.

Error

Get the last error from one block in an automation. This is used for custom error handling in an automation.

{ $.state.blockName.error }

Example error:

Example to get the full error object :

{ $.state.getContact.error }

Examples to get one field from the above error:

{$.state.getContact.error.'response code'} —> result: 404 {$.state.getContact.error.'response body'.message} —> result: Invalid number for id

Make sure to add single quotes around "response code" and "response body" since these properties (keys) contain a space character!

Formula to get current account, bundle, template, automation or job

Following placeholders can be used to retrieve the guid and/or name of the current provider (SaaS partner), account, bundle, template, automation or job:

{ providerguid }
{ providername }
{ accountguid }
{ accountname }
{ accountexternalid }
{ bundleguid }
{ templateguid }
{ templatename }
{ blendguid } or { widgetguid }
{ blendname }
{ jobguid }

See Definitions for more info on each of the above entities.

Formula url (incoming webhook URL from Blend) and executiontoken

Get the unique URL (REST API endpoint) of an automation, that can be used to call the automation from an external system. This URL is typically used to configure a webhook in a system that needs to call the given Blend.

Usage: { url }

This placeholder is typically used in an output block (Show block) in the Setup of an automation, to communicate the unique URL of the automation to the user, so the user can configure it as a webhook in some other cloud application. Note that this is only needed for webhooks that are not natively supported byQlik Application Automation for OEM.

You can also get just the execution token of the webhook URL of an automation (REST API endpoint):

Usage: { executiontoken }

Note that in the above formula , the execution token is already included in the querystring.

Formula to get index of a loop

Get the index (0, 1, 2, 3...) from a loop. This formula can only be used inside a loop.

{ $.loopblockname.index }

Example in a loop of a block list contacts : { $.listContacts.index }

Hash

Creates a hash from a given string (text).

Usage: { hash:$.text, 'algorithm' }

Available algorithms: md4, md5, sha1, sha256, sha512. If you want to apply a different algorithm, use a Custom Code block instead.

Csv

Converts a flat JSON object with key/values into a single CSV line. Used to write data from a source to a CSV file on cloud storage (e.g. Dropbox, AWS S3 etc.).

Usage: { csv: $.object, 'columns', 'delimiter' }

• $.object needs to be a flat JSON object, which means keys with values, without nested properties or arrays (use formula flatten if needed)
• Columns: optional, a string or list (array) with the columns in the CSV file
• Delimiter: optional, a string for the delimeter to use in the CSV line, default is a comma

Example JSON objects:

$.myObject1:
{
  "key3": "val 003",
  "key1": "val 001"
}

$.myObject2:
{
  "key1": "val 011",
  "key2": "val 022",
  "key3": "val 033"
}

$.myObject3:
{
  "key1": "val 111",
  "key2": "val 222"
}

$.columns:
[ "key1", "key2", "key3" ]

Applying the following CSV formulas:

{csv: $.myObject1, $.columns} {csv: $.myObject2, $.columns} {csv: $.myObject3, $.columns}

Will provide following result (extra spaces added in first line of example for readability):

"val 001",         ,"val 003"
"val 011","val 022","val 033"
"val 111","val 222"

Note that the CSV formula allows you to preserve the order as well as the correct columns in a CSV file, whereas the order of keys can be different across objects and whereas some keys maybe missing.

Flatten

Flattens any single object to a new object with key/values without any nested properties. Used to write an object to e.g. a CSV file.

Usage: { flatten: $.myObject }

Example:

$.myObject:
{
  "name": "John",
  "address": {
    "city": "Paris"
  },
  "orders": [
    {"id": 55},
    {"id": 66}
  ]
}

The result of { flatten: $.myObject } is:

{
  "name": "John",
  "address.city": "Paris",
  "orders.0.id": 55,
  "orders.1.id": 66
}