Query for UDFs

You can include User Defined Fields (UDFs) in your table query.

For range and quantile UDFs:
- Add a udfs section where you define the structure of the range or quantile
- Include your UDF in the dimensions.
For other UDF formats, include a where clause in your query.

For more details about range and quantile UDFs, see Ranges and Quantiles.

Range UDFs

Following is an example of a table query that defines a range UDF:

CODE

{
  "database" : "str:database:bank",
  "measures" : [ "str:count:bank:F_Customer" ],
  "udfs": {
		"myRange":{
    		"type": "RANGE",
    		"from": 1,
    		"to": 500,
    		"increment": 100,
    		"measure": "str:measure:bank:F_Customer:Cust_Profit",
    		"label": "My Range",
    		"toInclusive": true
		}		
	},
  "dimensions" : [                  
        [ "str:field:bank:F_Customer:Gender" ], [ "str:udf:myRange" ]
  ]
}

Where:

myRange

This is an ID for the UDF; you can replace it with any string.

You need to use this ID in the dimensions section to add the UDF to the query in the form str:udf:<ID>. In the example above, the ID has been set to myRange so the dimensions section needs to include str:udf:myRange.

type

Set to RANGE for a range UDF.

from

The starting value for the set of ranges.

to

The end value for the set of ranges.

increment

The size of each range.

measure

The Open Data ID of the measure you want to create a range for. This needs to match the ID format this is returned by the /schema endpoint.

label

A label (display name) for the UDF that will be returned in the query results.

toInclusive

Set this to either true or false. It indicates how the upper boundary of each range is included. For example the above configuration would create the following range buckets, depending on the value of toInclusive:

true	false
1 or less	Less than 1
More than 1 to 101	1 to less than 101
More than 101 to 201	101 to less than 201
More than 201 to 301	201 to less than 301
More than 301 to 401	301 to less than 401
More than 401	401 or more

Quantile UDFs

Following is an example of a table query that defines a quantile UDF:

CODE

{
  "database" : "str:database:bank",
  "measures" : [ "str:count:bank:F_Customer" ],
	  "udfs": {
		"myRange": { 
    		"type": "QUANTILE",
    		"label": "My Quantile",
    		"measure": "str:measure:bank:F_Customer:Cust_Profit",
    		"distribution": "str:count:bank:F_Customer",
    		"ranges": 5
		}		
	},
  "dimensions" : [                  
        [ "str:field:bank:F_Customer:Gender" ], [ "str:udf:myRange" ]
  ]
}

Where:

myRange	This is an ID for the UDF; you can replace it with any string. You need to use this ID in the `dimensions` section to add the UDF to the query in the form `str:udf:<ID>`. In the example above, the ID has been set to `myRange` so the `dimensions` section needs to include `str:udf:myRange`.
type	Set to `QUANTILE` for a quantile UDF.
label	A label (display name) for the UDF that will be returned in the query results.
measure	The Open Data ID of the measure you want to create a quantile for. This needs to match the ID format this is returned by the `/schema` endpoint.
distribution	The measure or count to use to determine the distribution of the quantile. This can be any measure or count, but must return positive values for all records. It is equivalent to the Equal Distribution Of setting in SuperWEB2. See Ranges and Quantiles for more details.
ranges	The number of quantile ranges to create. By default you can choose between 2 and 10, but these limits can be configured by the administrator on a per-dataset basis using the SuperADMIN `ranges` command.

Other UDFs (Where Clause)

For other UDF types, you can include a where clause in your query. Following are some examples of valid queries. See below for details of the query format.

Example: Single UDF - Individuals who are Male or Married

CODE

{
  "database": "str:database:bank",
  "measures": [ "str:count:bank:F_Customer" ],
  "where": {
    "operator": "OR",
    "conditions": [
      {
        "operator": "EQUAL",
        "field": "str:field:bank:F_Customer:Gender",
        "value": "Male"
      },
      {
        "operator": "EQUAL",
        "field": "str:field:bank:F_Customer:Marital_Status",
        "value": "Married"
      }
    ]
  }
}

Example: Individuals who are Single or Unmarried and live in postcodes 2000 or 3000

CODE

{
  "database": "str:database:bank",
  "measures": [ "str:count:bank:F_Customer" ],
  "dimensions": [
    [ "str:valueset:bank:F_Customer:Area:C_State" ]
  ],
  "where": {
    "operator": "AND",
    "conditions": [
      {
        "operator": "IN",
        "field": "str:valueset:bank:F_Customer:Area:C_State",
        "values": [ "Victoria", "New South Wales" ]
      },
      {
        "operator": "IN",
        "field": "str:valueset:bank:F_Customer:Area:C_Geography_0",
        "values": [ "2000", "3000" ]
      },
      {
        "operator": "IN",
        "field": "str:field:bank:F_Customer:Marital_Status",
        "values": [ "Single", "Unmarried" ]
      }
    ]
  }
}

Example: Using Greater Than and Less Than

CODE

{
  "database": "str:database:bank",
  "measures": [ "str:count:bank:F_Customer" ],
  "dimensions": [["str:valueset:bank:F_Customer:Gender:C_Gender"]],
  "where": {
    "operator": "OR",
    "conditions": [
      {
        "operator": "GREATER_THAN",
        "field": "str:measure:bank:F_Account:Acc_Profit",
        "value": 10
      },
      {
        "operator": "LESS_THAN",
        "field": "str:measure:bank:F_Account:Avg_Acc_Balance",
        "value": -20
      }
    ]
  }
}

Example: Use of BETWEEN Operator

CODE

{
  "database": "str:database:bank",
  "measures": [ "str:count:bank:F_Customer" ],
  "dimensions": [["str:valueset:bank:F_Customer:Gender:C_Gender"]],
  "where": {
    "operator": "OR",
    "conditions": [
      {
        "operator": "BETWEEN",
        "field": "str:measure:bank:F_Account:Acc_Profit",
        "value1": 10,
        "value2": 20
      }
    ]
  }
}

Where Clause Query Format

operator

The logical operator applied to the conditions. One of the following:

OR
AND

conditions

A list of conditions:

operator

One of the following:

EQUAL
BETWEEN
IN
GREATER_THAN
GREATER_THAN_OR_EQUAL
LESS_THAN
LESS_THAN_OR_EQUAL

field

The ID of the field or measure.

value
values
value1
value2

The required value, values, or range of values:

Most operators take a single value item.
When using the BETWEEN operator, use value1 and value2 instead of value to set the lower and upper boundaries.
When using IN, use values followed by a comma separated list of values, enclosed in square brackets.