You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.

Learn more about the conversion functions TEXT, INTEGER, NUMBER, CURRENCY, PERCENTAGE, DATE, DATETIME, BOOLEAN. 

Convert a value of one data type into a value of another data type using conversion functions. The following conversion functions are available:

  • from string to integer, number, currency, percentage, date, dateTime or Boolean

  • from integer to string, number, currency or percentage

  • from number to string, integer, currency or percentage

  • from currency to string, integer or number

  • from percentage to string, integer or number

  • from date to string or dateTime

  • from dateTime to string or date

  • from Boolean to string, integer or number

Overview

FunctionDescription
TEXTConverts a value of another data type into a string. 
INTEGERConverts a value of another data type into an integer. 
NUMBERConverts a value of another data type into a number. 
CURRENCYConverts a value of another data type into a currency.  
PERCENTAGEConverts a value of another data type into a percentage. 
DATEConverts a value of another data type into a date. 
DATETIMEConverts a value of another data type into a datetime. 
BOOLEANConverts a value of a string into a boolean. 

Output formats in the runtime. 

Functions

TEXT


Converts a value of another data type into a string. 


Syntax

TEXT(integer value, [format], [locale])
TEXT(number value, [format], [locale])
TEXT(currency value, [format], [locale])
TEXT(percentage value, [format], [locale])
TEXT(date value, [format])
TEXT(dateTime value, [format])
TEXT(Boolean value, [format])


Inputs

  • value - The value of the data type that is converted to string.

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. “00.00”.

  • locale (optional) - Locale specifies a combination of language and country. A locale in the option set will override the one that is used as default in the project. Commonly used are en-US, en-GB, nl-NL, nl-BE, fr-FR. The list of language codes can be found here. A list of country codes can be found here.


Default formats

For the TEXT function, the format specifies how the given datatype is converted towards a string. When no format is specified the defaults are as follows:

  • boolean to string - true;false
  • integer to string - no default format, the complete integer is set as string (no leading zeros)
  • number to string - rounded to one digit
  • percentage to string - rounded to one digit
  • currency to string - rounded to one digit
  • date to string - yyyy-MM-dd
  • datetime to string - yyyy-MM-dd hh:mm:ss

For more info on date and datetime formats, please see Date and time formats or patterns. 


Return type

  • string


Examples

  • TEXT(PERCENTAGE(1.2), “00.00”, “nl-NL”) results in a string value “01,20”

  • TEXT(123456.78,"#0,000","nl-NL") results in 123.457. A thousand separator has been added. The thousand separator is a point, as that is the defined one in the nl-NL locale.


Back to Top


INTEGER


Converts a value of another data type into an integer. 


Syntax

INTEGER(string value, [format], [locale])
INTEGER(number value)
INTEGER(currency value)
INTEGER(percentage value)
INTEGER(Boolean value)


Inputs

  • value - The value of the data type that is converted to integer. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. "00.00".

  • locale (optional) - Locale specifies a combination of language and country. A locale in the option set will override the one that is used as default in the project. Commonly used are en-US, en-GB, nl-NL, nl-BE, fr-FR. The list of language codes can be found here. A list of country codes can be found here.


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • string to integer - no default format, the complete string is parsed to integer (leading zeros are possible and will be removed). 


Return type

  • integer


Examples

  • INTEGER(“1,0”, ”#0.0”, “nl-NL”) results in an integer value 1. 

Back to Top


NUMBER


Converts a value of another data type into a number. 


Syntax

NUMBER(string value, [format], [locale])
NUMBER(integer value)
NUMBER(currency value)
NUMBER(percentage value)
NUMBER(Boolean value)


Inputs

  • value - The value of the data type that is converted to number. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. “00.00”.

  • locale (optional) - Locale specifies a combination of language and country. A locale in the option set will override the one that is used as default in the project. Commonly used are en-US, en-GB, nl-NL, nl-BE, fr-FR. The list of language codes can be found here. A list of country codes can be found here.


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • string to number - 4 digits. 


Return type

  • number


Examples

  • NUMBER(TRUE) results in a number value 1.0000

Back to Top


CURRENCY


Converts a value of another data type into a currency.  


Syntax

CURRENCY(string value, [format], [locale])
CURRENCY(integer value)
CURRENCY(number value)


Inputs

  • value - The value of the data type that is converted to currency. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. “00.00”.

  • locale (optional) - Locale specifies a combination of language and country. A locale in the option set will override the one that is used as default in the project. Commonly used are en-US, en-GB, nl-NL, nl-BE, fr-FR. The list of language codes can be found here. A list of country codes can be found here.


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • currency - 2 digits. 


Return type

  • currency


Examples

  • CURRENCY(“1.2”, “invalidPattern”) results in a currency value 1.20 (an unknown pattern will be ignored)

Back to Top


PERCENTAGE


Converts a value of another data type into a percentage. 


Syntax

PERCENTAGE(string, [format], [locale])
PERCENTAGE(integer)
PERCENTAGE(number)


Inputs

  • value - The value of the data type that is converted to percentage. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. “00.00”.

  • locale (optional) - Locale specifies a combination of language and country. A locale in the option set will override the one that is used as default in the project. Commonly used are en-US, en-GB, nl-NL, nl-BE, fr-FR. The list of language codes can be found here. A list of country codes can be found here.


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • string to percentage - 2 digits. 


Return type

  • percentage


Examples

  • PERCENTAGE ("22") results in a percentage value of 22,00 (default format is used). 

Back to Top


DATE


Converts a value of another data type into a date. 


Syntax

DATE(string value, [format])
DATE(dateTime value)


Inputs

  • value - The value of the data type that is converted to date. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. "yyyy-MM-dd". For more info on specifying date format patterns, please see Date and time formats or patterns. 


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • string to date - yyyy-MM-dd. 


Return type

  • date


Examples

  • DATE("2021-02-18") results in a date value of 18-02-2021 (default format is used). 
  • DATE("01/01/2021", "dd/MM/yyyy") results in a date value of 01-01-2021 (specified format is used). 

Back to Top


DATETIME


Converts a value of another data type into a datetime. 


Syntax

DATETIME(string value, [format])
DATETIME(date value)


Inputs

  • value - The value of the data type that is converted to datetime. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. “yyyy-MM-dd hh:mm:ss”. For more info on specifying datetime format patterns, please see Date and time formats or patterns. 


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • string to datetime - yyyy-MM-dd hh:mm:ss. 


Return type

  • date


Examples

  • DATETIME("2021-02-18 12:55:34") results in a datetime value of 18-02-2021 12:55:34 (default format is used). 
  • DATETIME("19861221 12:02:56", "yyyyMMdd HH:mm:ss") results in a datetime value of 21-12-1986 12:02:56 (specified format is used). 

Back to Top


BOOLEAN


Converts a value of a string into a boolean. 


Syntax

BOOLEAN(string, [format])


Inputs

  • string - The string value that is converted to boolean. 

  • format (optional) - The format used for conversion. Format must be enclosed by quotes, e.g. ”1;0”. 


Default formats

For all conversion functions that use a string as parameter, the [format] describes the format in which the input string value parameter is in. When not specifying a format, by default this format is used: 

  • string to boolean - true;false. 


Return type

  • boolean


Examples

  • BOOLEAN(“1”,”1;0”) results in a boolean value TRUE.  

Back to Top


Output formats in the runtime


Please see the Reference Guide: Language Formats to change how the runtime displays attributes. 

3 Comments

  1. Unknown User (i.van.der.hoeven)

    I want to convert a multivalued string ( ["1", "16", "18", "10", "11", "0"] ) to integers. Any suggestions how I could do that? INTEGER does not support array inputs (in BQ 11.0, anyway... )

    1. Frank van der Vaart

      Hi Ivo,

      You can repeat over multivalued attributes (introduced in Blueriq 10.4) and catch each string value in a (singlevalued) string attribute, convert it to integer and add it to a multivalued integer. I did a small test, and it works as expected (smile).

      You'll need the LIST function to add the first integer.

      ISUNKNOWN(
        UNION( Entity.MultiInteger , INTEGER( Entity.SingleString ) ),
        LIST( INTEGER( Entity.SingleString ) )
      )

  2. Unknown User (b.verzuu)

    Hi Frank van der Vaart,

    Thanks for responding! I think I've found the reason why Ivo asked this question. 

    It was needed to, one way or another, transform a single string value "1000-2000-3000" to a multivalued INT. There is no other way to do this now without iterating through the whole list one by one.

    Since the order of the items in the list is not relevant to these rules, I tried using the INTEGER( value, format, locale) function to somehow parse the string to a multivalued int. Combining SPLIT() and INTEGER() doesn't seem to work either because it tries to resolve a multivalued value into a singlevalued one instead parsing each single value into a list containing integers.

    In my opinion it's too bad we have to use repeat flowing for this since this approach forces me to "capture" this logic in a certain "place and time" within the flowing of my application. It would've been ideal if this would somehow be possible to do without having to model a flow around it. That's my two cents.

    Thanks again though, I'll be implementing this solution!

Didn't find the answer you were looking for? Try the advanced search!