Define Filter dialog

Defines the filter conditions.

You can specify row filters in two modes:

Simple mode: Use a simple template to define the filter, choosing a column, an inequality (for example, ">" or "between"), and a value (for example, a literal value or a column expression).
Script mode: Enter almost any set of filters by using SQL or Pig script.

Note: If you switch from Simple mode to Script mode, your filters are converted to script. However, your script is lost if you switch from Script mode to Simple mode.

Simple Mode

For Simple Mode, you can add multiple condition filters by clicking the add new filter link.

To define a filter, choose a column, a condition, and a value or expression.

AND/OR: You must also specify whether all filters must be met for each row (AND conditions), or just one of the filters (OR conditions).

Simple Mode Conditions

Condition	Database	Hadoop
=	Yes	Yes
<>	Yes	Yes
>	Yes	Yes
<	Yes	Yes
>=	Yes	Yes
<=	Yes	Yes
contains	Yes	Yes
between	No	Yes
is null	Yes	Yes
is not null	Yes	Yes
is blank	No	Yes

If your filter value is not numeric, you must add single quotes; for example, status = 'Active'.
If your filter value is numeric or an expression, you do not need to use quotes (for example, start_date = current_date, or customer_id > 37).
If your filter value is a column and the column name contains spaces or uppercase characters, you must add double quotes (for example, "Start Date" - 1)

Script Mode

In Script mode, filters are added as a "where" clause in the SQL or Pig scripting languages.

You do not need to add the WHERE keyword.
You can combine multiple filters with boolean expressions (AND, OR, and so on) and parentheses.
Any expression that fits within a WHERE clause in SQL (for DB) or Pig (for HD) can be used.

Note: For Hadoop data, if your filter value is a DateTime value, it can be compared to any other ISO-formatted DateTime Value. For details, see ISO DateTime Format.

Additionally, for Hadoop, any DateTime Pig Function can be applied to a DateTime field, such as GetMonth(datetime) or GetDay(datetime), and the result can then be used as Row Filter criteria.

You can find the full list of available DateTime-related Pig Functions at Apache Pig DateTime Functions.

Because some Pig functions have issues handling null values, using the TIBCO Data Science – Team Studio alternatives for certain functions is recommended. Just append "Alpine" to the method name; for example, "DaysBetween" becomes "DaysBetweenAlpine".

Pig DateTime Functions	Alternative Pig DateTime Functions
`AddDuration`	`AddDurationAlpine`
`SubtractDuration`	`SubtractDurationAlpine`
`MilliSecondsBetween`	`MilliSecondsBetweenAlpine`
`SecondsBetween`	`SecondsBetweenAlpine`
`MinutesBetween`	`MinutesBetweenAlpine`
`HoursBetween`	`HoursBetweenAlpine`
`DaysBetween`	`DaysBetweenAlpine`
`WeeksBetween`	`WeeksBetweenAlpine`
`MonthsBetween`	`MonthsBetweenAlpine`
`YearsBetween`	`YearsBetweenAlpine`

Did you find this helpful?

Yes No