cPlan Filters

To filter on a combination of values, use a form similar to:

cplan "/cplans/autoexample.cplan" {

filter `value("Make")="BMW"`

filter `value("Year")="2015"`

}

The data returned is for make=BMW AND year=2015. Similarly:

cplan {

cbase-input "../demo_drs/demo_drs.cbase"

filter `value("Product Family") != "Cheese"`

filter `value("Product Family") != "Yogurt"`

}

Here too, the AND is implied: product families cheese and yogurt are filtered out.

To filter on a list of values, use the following form:

cplan "/cplans/autoexample.cplan" {

filter `is_in(value("Make"),"Dodge", "Fiat", "Jeep")`

}

This example returns records for the Dodge, Fiat, and Jeep car makes, that is, make is Dodge OR make is Fiat OR make is Jeep.

Another example:

cplan "/cplans/autoexample.cplan" {

`filter `value("Make")="Jeep" or value("Group")="BMW Group"`

}

This example returns records for the Jeep car make and the BMW Group, that is, make is Jeep OR group is BMW.

To filter out null records, use the notnull() function on an appropriate column. For example:

filter `notnull(value("Vehicles Sold"))`

This is equivalent to filter `value("Vehicles Sold") !=""`

Or check for the negative using the is_null() function. For example:

filter ```

not (is_null(value("Last Year")) and

is_null(value("This Year")))

```

To include multiple criteria in the filter, use Boolean and/or operators in the filter expression. Here are some examples:

filter ```value("CostCenterRollUp") = "Domestic" and

(value("BookingYear") ="2014" or value("BookingYear") ="2013") and

value("Iscommissionable") = 1```

 

filter `value("Employee_ID") = 1 or value("Employee_ID") = 2`

 

cplan "/cplans/customerbooking.cplan"{

filter ```

value("BookingYear") = "2014" and

is_in(value("BookingMonth"), 1,2,3)```

}

When filtering on a period column, be mindful of the type—most likely the column is of type period, not string. For example:

filter `value("Period") = period(<calendar string>, <period string>, <date string>, <format string>)

Where standard is the calendar type and year-month is the period type:

filter `value("Period") = period("standard", "year-month", "201901", "YYYYMM")

This filter expression returns records for the period January 2019.

A cPlan can also be defined to use parameters so that the filter is applied on the input file using param_match().

This example uses the passed parameters to filter the input files while preparing the cPlan.

cplan {
  take-parameter "Sales Team" selection=true { 
    default-all-values
  } 
  take-parameter "Sales Manager" selection=true {
    default-all-values 
  } 
  take-parameter "Brand" selection=true {
    default-all-values
  } 
  input "sales.cbase" name="subset_cost" {
    filter `param_match("Sales Team")` 
    filter `param_match("Sales Manager")`
    rename "Cost" "Filtered Cost" 
    rename "Bottles" "DISCARD Bottles" 
    rename "Cases" "DISCARD Cases" 
    rename "Revenue" "DISCARD Revenue" 
  }
  input "sales.cbase" name="subset_revenue" {
    filter `param_match("Sales Team")`
    filter `param_match("Sales Manager")`
    filter `param_match("Brand")` 
    rename "Revenue" "Filtered Revenue"
    rename "Cost" "BAD Cost" 
    rename "Bottles" "BAD Bottles" 
    rename "Cases" "BAD Cases" 
  } 
  input "sales.cbase" name="full_data" {
    rename "Revenue" "ALL Revenue" 
    rename "Cost" "ALL Cost" 
    rename "Bottles" "ALL Bottles" 
    rename "Cases" "ALL Cases" 
  } 
  calc "Rate FILTERED" ```
    calc("Filtered Revenue")*100/calc("Filtered Cost")
    ``` 
  calc "Rate ALL" ```
    calc("ALL Revenue")*100/calc("ALL Cost")
    ```
}