How to Filter by Partition in a Workbook - Advanced Filtering
When an Import Job or Data Link is partitioned in Datameer, users may utilize the Filter by Partition feature of a workbook. One powerful type of filter for partitions in a Workbook is Advanced Filtering.
An Advanced Filter allows a user to create a Datameer function including a $partition variable that evaluates to a Boolean value. All partitions will be tested using the $partition variable and those that evaluate to True will be included in the Workbook's data set.
Understanding the $partition Variable for Different Partition Resolutions
When a partition is created in Datameer for an Import Job or Data Link, there are four different partition resolutions that may be configured: YEAR, MONTH, DAY or HOUR. The $partition variable is a DATE type object in Datameer. This variable is set to the start of the partition window. Depending on the resolution of the partitions, the $partition variable takes on different DATE values.
For example, suppose that Apache log data is being imported from July 4, 2014 22:00:00 until July 4, 2014 22:59:59. The following table describes how an Import Job would partition data for each possible resolution. Additionally it describes what the corresponding $partition variable would be for this data set:
|Partition Resolution||Partition Range (Inclusive)||$partition Variable Value|
|YEAR||January 1, 2014 00:00:00 - December 31, 2014 23:59:59||January 1, 2014 00:00:00|
|MONTH||July 1, 2014 00:00:00 - July 31, 2014 23:59:59||July 1, 2014 00:00:00|
|DAY||July 4, 2014 00:00:00 - July 4, 2014 23:59:59||July 4, 2014 00:00:00|
|HOUR||July 4, 2014 22:00:00 - July 4, 2014 22:59:59||July 4, 2014 22:00:00|
The $lastpartition Variable
In Datameer 2.1.7 and higher, another variable is available for use when defining an Advanced Filter for partitions in a Workbook. This variable, $latestpartition, represents the most recent partition that contains data.
Advanced Filter Example - Selecting a Specific Partition
For example, let's suppose that a user would like to select a partition that contains the time from precisely 1 day ago. The following table describes how to configure an Advanced Filter to select the partition that includes precisely 1 day ago:
|Partition Resolution||Advanced Filter Formula to Select the Partition for 1 Day Ago|
YEAR($partition) == YEAR(NOW()-1d)
YEAR($partition) == YEAR(NOW()-1d) && MONTH($partition) == MONTH(NOW()-1d)
YEAR($partition) == YEAR(NOW()-1d) && MONTH($partition) == MONTH(NOW()-1d) && DAY($partition) == DAY(NOW()-1d)
YEAR($partition) == YEAR(NOW()-1d) && MONTH($partition) == MONTH(NOW()-1d) && DAY($partition) == DAY(NOW()-1d) && HOUR($partition) == HOUR(NOW()-1d)
Of note, the following function would not produce the expected result in all circumstances:
$partition == NOW()-1d
This is also thru for
$partition == TODAY()-1d