Build expressions - Data360_DQ+ - Latest

Understanding functions

You can use Data360 DQ+ functions when you are building expressions throughout the application to apply algorithms to your data.

 

For help with a specific function, you can perform a filtered search on the list of fields in a data store or case store on the Fields tab in order to quickly locate the fields needed to modify:

 

The inputs and outputs that are required for a function to work properly are always listed in the first, blue line of the function's help text. The formatting is as follows:

FUNCTION(parameterDescription: paramaterDataType [,optionalParameterDescription: optionalParameterDataType]):OutputDataType

For example, the DATE function requires a year, month and day parameter, all of which must be a Number data type. Then, optionally, you can include a timeZone parameter of the String data type. The DATE function outputs Date type data:

DATE(year:Number, month:Number, day:Number [,timeZone:String]):Date

Testing expressions

When building expressions, you can use the Test panel shown on the right side of the Expression Editor.

Once you have written an expression, you can experiment by entering values for fields used in the expression. Clicking Run Test will output the result of the expression with those values in the bottom of the testing panel.

Understanding data types

When writing functions within the function bar, the data type will be assigned automatically based on the function in use.

If you are using the pop-out editor, you can use the search bar to search and manually select the output type. If a manual selection is made, the output type must conform to what the function was designed to output. Some functions will allow more than one output type, for example, with the DATE function, you could use the Date or DateTime output type.

Number data types

Functions that accept Number type parameters and/or output the Number data type will work with any of the following data types:

• Big Integer
• Currency
• Decimal
• Floating Point
• Integer

When selecting a numeric data type, it is important to consider how it will affect your result. For example, consider the different outputs created by the PI() function, when various number output data types are used:

• Big Integer: PI() = 3
• Currency: PI() = $3.14
• Decimal: PI() = 3.14
• Floating Point: PI() = 3.14
• Integer: PI() = 3

String data

When referencing the name of a field that contains strings for the function to read, 'single quotes' are not needed around the name of the field. When referencing a literal string value for the function to read, you must use 'single quotes' around the string.

For example, the following function looks for 'Robert' in the strings of a data set's nameField and returns True for each record where 'Robert' is found:

CONTAINS(nameField, 'Robert')

Date formatting

All date formatting is based on Java date formatting. For additional information, see Class SimpleDateFormat.

Time zone formats

When working with functions that take a timezone parameter, timezones are treated as literal strings and must be surrounded by 'single quotes'. For example, the following function returns the date 12/25/2015 in the US Central timezone:

DATE(2015,12,25,'US/Central')

Supported timezones

Africa/Abidjan

Africa/Accra

Africa/Addis_Ababa

Africa/Algiers

Africa/Asmara

Africa/Asmera

Africa/Bamako

Africa/Bangui

Africa/Banjul

Africa/Bissau

Africa/Blantyre

Africa/Brazzaville

Africa/Bujumbura

Africa/Cairo

Africa/Casablanca

Africa/Ceuta

Africa/Conakry

Africa/Dakar

Africa/Dar_es_Salaam

Africa/Djibouti

Africa/Douala

Africa/El_Aaiun

Africa/Freetown

Africa/Gaborone

Africa/Harare

Africa/Johannesburg

Africa/Juba

Africa/Kampala

Africa/Khartoum

Africa/Kigali

Africa/Kinshasa

Africa/Lagos

Africa/Libreville

Africa/Lome

Africa/Luanda

Africa/Lubumbashi

Africa/Lusaka

Africa/Malabo

Africa/Maputo

Africa/Maseru

Africa/Mbabane

Africa/Mogadishu

Africa/Monrovia

Africa/Nairobi

Africa/Ndjamena

Africa/Niamey

Africa/Nouakchott

Africa/Ouagadougou

Africa/Porto-Novo

Africa/Sao_Tome

Africa/Timbuktu

Africa/Tripoli

Africa/Tunis

Africa/Windhoek

America/Adak

America/Anchorage

America/Anguilla

America/Antigua

America/Araguaina

America/Argentina/Buenos_Aires

America/Argentina/Catamarca

America/Argentina/ComodRivadavia

America/Argentina/Cordoba

America/Argentina/Jujuy

America/Argentina/La_Rioja

America/Argentina/Mendoza

America/Argentina/Rio_Gallegos

America/Argentina/Salta

America/Argentina/San_Juan

America/Argentina/San_Luis

America/Argentina/Tucuman

America/Argentina/Ushuaia

America/Aruba

America/Asuncion

America/Atikokan

America/Atka

America/Bahia

America/Bahia_Banderas

America/Barbados

America/Belem

America/Belize

America/Blanc-Sablon

America/Boa_Vista

America/Bogota

America/Boise

America/Buenos_Aires

America/Cambridge_Bay

America/Campo_Grande

America/Cancun

America/Caracas

America/Catamarca

America/Cayenne

America/Cayman

America/Chicago

America/Chihuahua

America/Coral_Harbour

America/Cordoba

America/Costa_Rica

America/Creston

America/Cuiaba

America/Curacao

America/Danmarkshavn

America/Dawson

America/Dawson_Creek

America/Denver

America/Detroit

America/Dominica

America/Edmonton

America/Eirunepe

America/El_Salvador

America/Ensenada

America/Fort_Wayne

America/Fortaleza

America/Glace_Bay

America/Godthab

America/Goose_Bay

America/Grand_Turk

America/Grenada

America/Guadeloupe

America/Guatemala

America/Guayaquil

America/Guyana

America/Halifax

America/Havana

America/Hermosillo

America/Indiana/Indianapolis

America/Indiana/Knox

America/Indiana/Marengo

America/Indiana/Petersburg

America/Indiana/Tell_City

America/Indiana/Vevay

America/Indiana/Vincennes

America/Indiana/Winamac

America/Indianapolis

America/Inuvik

America/Iqaluit

America/Jamaica

America/Jujuy

America/Juneau

America/Kentucky/Louisville

America/Kentucky/Monticello

America/Knox_IN

America/Kralendijk

America/La_Paz

America/Lima

America/Los_Angeles

America/Louisville

America/Lower_Princes

America/Maceio

America/Managua

America/Manaus

America/Marigot

America/Martinique

America/Matamoros

America/Mazatlan

America/Mendoza

America/Menominee

America/Merida

America/Metlakatla

America/Mexico_City

America/Miquelon

America/Moncton

America/Monterrey

America/Montevideo

America/Montreal

America/Montserrat

America/Nassau

America/New_York

America/Nipigon

America/Nome

America/Noronha

America/North_Dakota/Beulah

America/North_Dakota/Center

America/North_Dakota/New_Salem

America/Ojinaga

America/Panama

America/Pangnirtung

America/Paramaribo

America/Phoenix

America/Port-au-Prince

America/Port_of_Spain

America/Porto_Acre

America/Porto_Velho

America/Puerto_Rico

America/Rainy_River

America/Rankin_Inlet

America/Recife

America/Regina

America/Resolute

America/Rio_Branco

America/Rosario

America/Santa_Isabel

America/Santarem

America/Santiago

America/Santo_Domingo

America/Sao_Paulo

America/Scoresbysund

America/Shiprock

America/Sitka]

America/St_Barthelemy

America/St_Johns

America/St_Kitts

America/St_Lucia

America/St_Thomas

America/St_Vincent

America/Swift_Current

America/Tegucigalpa

America/Thule

America/Thunder_Bay

America/Tijuana

America/Toronto

America/Tortola

America/Vancouver

America/Virgin

America/Whitehorse

America/Winnipeg

America/Yakutat

America/Yellowknife

Antarctica/Casey

Antarctica/Davis

Antarctica/DumontDUrville

Antarctica/Macquarie

Antarctica/Mawson

Antarctica/McMurdo

Antarctica/Palmer

Antarctica/Rothera

Antarctica/South_Pole

Antarctica/Syowa

Antarctica/Troll

Antarctica/Vostok

Arctic/Longyearbyen

Asia/Aden

Asia/Almaty

Asia/Amman

Asia/Anadyr

Asia/Aqtau

Asia/Aqtobe

Asia/Ashgabat

Asia/Ashkhabad

Asia/Baghdad

Asia/Bahrain

Asia/Baku

Asia/Bangkok

Asia/Beirut

Asia/Bishkek

Asia/Brunei

Asia/Calcutta

Asia/Chita

Asia/Choibalsan

Asia/Chongqing

Asia/Chungking

Asia/Colombo

Asia/Dacca

Asia/Damascus

Asia/Dhaka

Asia/Dili

Asia/Dubai

Asia/Dushanbe

Asia/Gaza

Asia/Harbin

Asia/Hebron

Asia/Ho_Chi_Minh

Asia/Hong_Kong

Asia/Hovd

Asia/Irkutsk

Asia/Istanbul

Asia/Jakarta

Asia/Jayapura

Asia/Jerusalem

Asia/Kabul

Asia/Kamchatka

Asia/Karachi

Asia/Kashgar

Asia/Kathmandu

Asia/Katmandu

Asia/Khandyga

Asia/Kolkata

Asia/Krasnoyarsk

Asia/Kuala_Lumpur

Asia/Kuching

Asia/Kuwait

Asia/Macao

Asia/Macau

Asia/Magadan

Asia/Makassar

Asia/Manila

Asia/Muscat

Asia/Nicosia

Asia/Novokuznetsk

Asia/Novosibirsk

Asia/Omsk

Asia/Oral

Asia/Phnom_Penh

Asia/Pontianak

Asia/Pyongyang

Asia/Qatar

Asia/Qyzylorda

Asia/Rangoon

Asia/Riyadh

Asia/Saigon

Asia/Sakhalin

Asia/Samarkand

Asia/Seoul

Asia/Shanghai

Asia/Singapore

Asia/Srednekolymsk

Asia/Taipei

Asia/Tashkent

Asia/Tbilisi

Asia/Tehran

Asia/Tel_Aviv

Asia/Thimbu

Asia/Thimphu

Asia/Tokyo

Asia/Ujung_Pandang

Asia/Ulaanbaatar

Asia/Ulan_Bator

Asia/Urumqi

Asia/Ust-Nera

Asia/Vientiane

Asia/Vladivostok

Asia/Yakutsk

Asia/Yekaterinburg

Asia/Yerevan

Atlantic/Azores

Atlantic/Bermuda

Atlantic/Canary

Atlantic/Cape_Verde

Atlantic/Faeroe

Atlantic/Faroe

Atlantic/Jan_Mayen

Atlantic/Madeira

Atlantic/Reykjavik

Atlantic/South_Georgia

Atlantic/St_Helena

Atlantic/Stanley

Australia/ACT

Australia/Adelaide

Australia/Brisbane

Australia/Broken_Hill

Australia/Canberra

Australia/Currie

Australia/Darwin

Australia/Eucla

Australia/Hobart

Australia/LHI

Australia/Lindeman

Australia/Lord_Howe

Australia/Melbourne

Australia/NSW

Australia/North

Australia/Perth

Australia/Queensland

Australia/South

Australia/Sydney

Australia/Tasmania

Australia/Victoria

Australia/West

Australia/Yancowinna

Brazil/Acre

Brazil/DeNoronha

Brazil/East

Brazil/West

CET

CST6CDT

Canada/Atlantic

Canada/Central

Canada/East-Saskatchewan

Canada/Eastern

Canada/Mountain

Canada/Newfoundland

Canada/Pacific

Canada/Saskatchewan

Canada/Yukon

Chile/Continental

Chile/EasterIsland

Cuba

EET

EST

EST5EDT

Egypt

Eire

Etc/GMT

Etc/GMT+0

Etc/GMT+1

Etc/GMT+10

Etc/GMT+11

Etc/GMT+12

Etc/GMT+2

Etc/GMT+3

Etc/GMT+4

Etc/GMT+5

Etc/GMT+6

Etc/GMT+7

Etc/GMT+8

Etc/GMT+9

Etc/GMT-0

Etc/GMT-1

Etc/GMT-10

Etc/GMT-11

Etc/GMT-12

Etc/GMT-13

Etc/GMT-14

Etc/GMT-2

Etc/GMT-3

Etc/GMT-4

Etc/GMT-5

Etc/GMT-6

Etc/GMT-7

Etc/GMT-8

Etc/GMT-9

Etc/GMT0

Etc/Greenwich

Etc/UCT

Etc/UTC

Etc/Universal

Etc/Zulu

Europe/Amsterdam

Europe/Andorra

Europe/Athens

Europe/Belfast

Europe/Belgrade

Europe/Berlin

Europe/Bratislava

Europe/Brussels

Europe/Bucharest

Europe/Budapest

Europe/Busingen

Europe/Chisinau

Europe/Copenhagen

Europe/Dublin

Europe/Gibraltar

Europe/Guernsey

Europe/Helsinki

Europe/Isle_of_Man

Europe/Istanbul

Europe/Jersey

Europe/Kaliningrad

Europe/Kiev

Europe/Lisbon

Europe/Ljubljana

Europe/London

Europe/Luxembourg

Europe/Madrid

Europe/Malta

Europe/Mariehamn

Europe/Minsk

Europe/Monaco

Europe/Moscow

Europe/Nicosia

Europe/Oslo

Europe/Paris

Europe/Podgorica

Europe/Prague

Europe/Riga

Europe/Rome

Europe/Samara

Europe/San_Marino

Europe/Sarajevo

Europe/Simferopol

Europe/Skopje

Europe/Sofia

Europe/Stockholm

Europe/Tallinn

Europe/Tirane

Europe/Tiraspol

Europe/Uzhgorod

Europe/Vaduz

Europe/Vatican

Europe/Vienna

Europe/Vilnius

Europe/Volgograd

Europe/Warsaw

Europe/Zagreb

Europe/Zaporozhye

Europe/Zurich

GB

GB-Eire

GMT

GMT+0

GMT-0

GMT0

Greenwich

HST

Hongkong

Iceland

Indian/Antananarivo

Indian/Chagos

Indian/Christmas

Indian/Cocos

Indian/Comoro

Indian/Kerguelen

Indian/Mahe

Indian/Maldives

Indian/Mauritius

Indian/Mayotte

Indian/Reunion

Iran

Israel

Jamaica

Japan

Kwajalein

Libya

MET

MST

MST7MDT

Mexico/BajaNorte

Mexico/BajaSur

Mexico/General

NZ

NZ-CHAT

Navajo

PRC

PST8PDT

Pacific/Apia

Pacific/Auckland

Pacific/Bougainville

Pacific/Chatham

Pacific/Chuuk

Pacific/Easter

Pacific/Efate

Pacific/Enderbury

Pacific/Fakaofo

Pacific/Fiji

Pacific/Funafuti

Pacific/Galapagos

Pacific/Gambier

Pacific/Guadalcanal

Pacific/Guam

Pacific/Honolulu

Pacific/Johnston

Pacific/Kiritimati

Pacific/Kosrae

Pacific/Kwajalein

Pacific/Majuro

Pacific/Marquesas

Pacific/Midway

Pacific/Nauru

Pacific/Niue

Pacific/Norfolk

Pacific/Noumea

Pacific/Pago_Pago

Pacific/Palau

Pacific/Pitcairn

Pacific/Pohnpei

Pacific/Ponape

Pacific/Port_Moresby

Pacific/Rarotonga

Pacific/Saipan

Pacific/Samoa

Pacific/Tahiti

Pacific/Tarawa

Pacific/Tongatapu

Pacific/Truk

Pacific/Wake

Pacific/Wallis

Pacific/Yap

Poland

Portugal

ROC

ROK

Singapore

Turkey

UCT

US/Alaska

US/Aleutian

US/Arizona

US/Central

US/East-Indiana

US/Eastern

US/Hawaii

US/Indiana-Starke

US/Michigan

US/Mountain

US/Pacific

US/Pacific-New

US/Samoa

UTC

Universal

W-SU

WET

Zulu