DataWind supports filtering of data source data to retain only a portion of eligible data. This article will show you how to perform data filtering on datasets.

Support the dataset Model Configuration before and after the configuration of data filtering, after the configuration of data filtering, the dataset will only be synchronized to meet the filter conditions.

The data filtering function can be configured when creating a new dataset, or when editing an existing dataset.

• Direct Connection Mode:

  	Before model configuration	After model configuration
  New dataset	Create a new dataset, enter the model configuration page, drag and drop the required data table to the right canvas, and click on the data table to see the "Data Filtering" function in the configuration area below.	In direct connection mode, filtering of data after model configuration is not supported. tip Direct-connected mode does not store intermediate data after model configuration, while extraction mode stores intermediate results locally, so only extraction mode supports such filtering.
  Existing dataset	Edit the dataset, enter the model configuration page, and click on any data table that needs to be filtered to see the "Data Filtering" function in the configuration area below.	In direct connection mode, filtering of data after model configuration is not supported. tip Direct-connected mode does not store intermediate data after model configuration, while extraction mode stores intermediate results locally, so only extraction mode supports such filtering.

• Extraction mode:

  	Before model configuration	After model configuration
  New dataset	Create a new dataset, enter the model configuration page, drag and drop the required data table to the right canvas, and click on the data table to see the "Data Filtering" function in the configuration area below.	Create a new dataset, enter the model configuration page, and click the "Data Filter" icon in the upper right corner to configure the filter conditions in the pop-up data filter configuration pop-up window.
  Existing dataset	Edit the dataset, enter the model configuration page, and click on any data table that needs to be filtered to see the "Data Filtering" function in the configuration area below.	Edit the dataset, enter the model configuration page, and after completing the model configuration modification operation, click the "Data Filter" icon in the upper right corner to configure the filter conditions in the pop-up data filter configuration pop-up window.

Before model configuration #

Filtering before model configuration is data filtering for a single data table.

Configuration conditions #

• Direct connection mode: Only "field filtering" for a single table is supported, and only "and" relationships are supported between multiple conditions. Each condition contains field, Operator, and value. For example: Primary Channel = Marketing Campaign.
  

• Extraction mode: Only single table "field filtering" is supported, including partitioned field filtering and non-partitioned field filtering, and only "and" relationships are supported between multiple conditions.

  tip

  Partitioning is a mechanism for splitting a large data table into smaller, independently manageable subtables (called "partitions") according to specific rules (such as time, geography, class, etc.). These subtables are still logically part of the same table, but may be physically stored in different locations for efficient querying and data management.

  

  Extraction Mode Field Filter Type	Introduction
  Partition filtering	For the extracted dataset, when the dataset model, the data source table with partition, click on the table node, you can find the "partition filter" option in the data filter. Partition filtering is a condition setting for the partition fields of a table (such as time fields, area fields), which is used to specify the specific partition range of synchronization, directly affecting the efficiency and scope of data synchronization. The partition filter still contains fields, operators, and values, but here the fields are partition fields and cannot be re-selected, and the operators only include =, in, and unrestricted. tip When the following system date variables are introduced, dynamic date queries are implemented: ${DATE} business time date, in the format: yyyy-MM-dd, such as: 2015-05-17 ${Date} Business time date, format: yyyyMMdd, such as: 20150526 ${HOUR} Gets the business time hour in the format: h, such as: 7 ${Hour} Gets the business time hour in the format: hh, such as: 07 ${t_date} [Trading day] time and date, format: yyyyMMdd, such as: 20150526 ${T_DATE} [Trading day] time date, format: yyyy-MM-dd, such as: 2015-05-17 ${prevMonthEnd_DATE} execution time at the end of last month, the format is yyyy-MM-dd ${prevMonthEnd_date} execution time at the end of last month, format: yyyyMMdd ${prevYearEnd_DATE} execution time at the end of the previous year, in the format: yyyy-MM-dd ${prevYearEnd_date} execution time at the end of the previous year, in the format: yyyyMMdd
  Non-partitioned filtering	For extracted datasets, click on the table node of any data source to find the "Non-Partition Filtering" option in the data filter. Non-partition filtering is a condition setting for other ordinary fields in the table except partition fields, which is used to filter specific data records that meet business requirements. It does not involve the partition structure of the data table and only filters the specific data content in a single table. Non-partitioned filter criteria contain fields, Operator, values, for example: user_id ≠ 11111111.

Delete condition #

Hover over the condition and click "Delete" behind the condition to delete the condition.

After model configuration #

Filtering after model configuration is to filter against the configured data model, and to support filtering of non-aggregated expression results across data sources.

Configuration conditions #

Configure multiple layers and/or conditions

• Multilayer conditions: including inner and outer layers.
  
  • Inner layer: After adding a filter condition, click "+ field condition" or "+ expression condition" below to add a juxtaposition condition.
  • Outer layer: When there are multiple filter conditions, click "+ Field Conditions" or "+ Expression Conditions" in the outer layer to add parallel condition groups.
• And or condition toggle: Click the and/or icon to the left of the condition group to change the and or logic between conditions.
  

Configure field conditions and expression conditions

• Field Condition: After selecting a field, filter the field values. Each condition contains a field, Operator, value, for example: Primary Channel = Marketing Campaign.
  
• Expression Condition: Write an expression that filters the data to a certain value. Users can double-click the selection field in the "Add Expression" pop-up window to construct the expression, and then return to the filter to add the operator of the expression and other relevant information.
  • For example:
    • Filter data that subtracts two metrics by more than 100: field A - field B with a value greater than 100.
      
    • Filter the data for two metrics with equal values: field A = field B, with the value selected as True.
      

Delete condition #

Hover over the condition and click "Delete" behind the condition to delete the condition.

Operator #