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 the 
(expression builder) icon from the field definition row. Expression builder 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
- OBFUSCATE
- 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
|
NOT |
Not |
|
IS NULL |
Check if x is null |
|
IS NOT NULL |
Check if x is not null |
|
* |
Matches zero or more characters |
|
/ |
Division |
|
% |
Returns the remainder of x divided by y (x%y) |
|
+ |
Addition |
|
- |
Subtraction |
|
< |
Less than |
|
<= |
Less than or equal to |
|
>= |
Greater than or equal to |
|
> |
Greater than |
|
== |
Equal |
|
!= |
Not equal |
|
AND |
And |
|
OR |
Or |
|
CASE WHEN THEN ELSE END |
Case <expression> [WHEN <expression> THEN <expression>] + [ELSE <expression>] END |
Sample Use
Provides a brief explanation of the expression functionality.
Expression
Expands/Collapses the expression syntax window.
Adding UDF packages to Qlik Catalog
User Defined Functions (UDFs) can be added to Qlik Catalog so that they show up and are available in the custom expression builder. To make them available, place the UDF jar in the classpath (podium/WEB-INF/lib) and restart Tomcat. Note that UDFs in the new jar will show up only if they satisfy the criteria by which Qlik Catalog 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 expression builder icon on the relevant field definition to open the expression builder modal.
Once the expression builder opens, select the appropriate Pig function or operator. The expression window populates with the selected operator:
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)
can be edited to become:
(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.
