Custom Expression Builder

The Custom Expression Builder provides specific functions and operators to define package operations. Selecting from dropdown menus, users select one or more fields and then choose applicable functions and operators. The functions auto-populate in the package control and execute in a Pig script.

The Expression Builder displays available Fields, Function, and Parameter option tabs for insertion into the Transform expression.

Accessing Custom Expression Builder

The expression builder is accessed by selecting its icon from the field definition row. It is available in Router, Filter and Transform controls.

Custom Expression Builder components

Fields: The expression builder extracts the fields from the source entity and presents them for selection in the function dropdown.

Function: Select from the following supported functions:

• ABS
• ACOS
• ASIN
• ATAN
• CBRT
• CEIL
• CONCAT
• ConvertDdMonYy
• ConvertMmDdYyyy
• ConvertYYYYMMDD
• COS
• COSH
• CurrentDateTimeFormatted
• CurrentDateTimeString
• DateAddDays
• DateCompare
• DateConvertDdMonYy
• DateConvertMmDdYyyy
• DateConvertYYYYMMDD
• DateCurrent
• DateDaysDifference
• DateFirstDayOfMonth
• DateGetWeekdayNumber
• DateIsBetweenExclusive
• DateIsBetweenInclusive
• DateIsEqual
• DateIsValidFormat
• DateParseFormat
• DateRollBackwardToWeekDayByWeek
• DateRollForwardToWeekDay
• DiceCoefficient
• DoubleAbs
• ENDSWITH
• EqualsIgnoreCase
• EXP
• FLOOR
• INDEXOF
• IS_DOUBLE
• IS_INTEGER
• IS_LONG
• IS_NULL
• IS_NULL_OR_EMPTY
• LAST_INDEX_OF
• LCFIRST
• LevenshteinDistance
• LOG
• LOG10
• LongestCommonSubsequence
• LOWER
• LTRIM
• NextSequence
• NVL
• RANDOM
• REGEX_EXTRACT
• REPLACE
• ROUND
• ROUND_TO
• RTRIM
• SIN
• SINH
• SIZE
• SPRINTF
• SQRT
• STARTSWITH
• SUBSTRING
• TAN
• TANH
• TO_DECIMAL
• TO_DOUBLE
• TO_INTEGER
• TO_LONG
• TO_STRING
• TRIM
• UCFIRST
• UniqueID
• UPPER

Operator

Sample Use

Provides a brief explanation of the expression functionality.

Expression

Expands/Collapses the Expression Syntax window.

Adding UDF packages to Qlik Data Catalyst

User Defined Functions (UDFs) can be added to Qlik Data Catalyst so that they show up and are made available in the Custom Expression Builder. This involves placing the UDF jar in the classpath (podium/WEB-INF/lib) and restarting Tomcat. Note that UDFs in the new jar will show up only if they satisfy the criteria by which Qlik Data Catalyst selects UDFs. For example, UDFs that take datatypes other than LONG and DOUBLE will be filtered out.  If UDFs are in a new package (not the base package), they must be included in core_env.property pig.udf.discovery.basepackages (as a comma-separated value) to be scanned by the Spring Framework.

PIG UDFS package discovery, core_env property:

pig.udf.discovery.basepackages:org.apache.pig,com.podiumdata.pigudf,datafu

Custom expression creation

Select the Custom Expression icon next to the field to be transformed.

Select the appropriate Pig function or operator. The expression window populates with the selected operator:

Select the expression builder icon on the relevant field definition to open the expression builder modal.

Replace the expression placeholders with relevant fields and values. Field names are inserted without quotes, single quotes are added around field values.

For this example:

fruit (CASE <expression> WHEN <expression> THEN <expression> ELSE <expression> END)

is edited to:

(CASE fruit WHEN 'macintosh' THEN 'apple' ELSE fruit END)

Adjust syntax then Validate the expression. The new expression appears in the field mapping panel.

Save, Validate, and Execute the dataflow.

Observe that the value macintosh in the Source Entity now displays as apple in the target entity.