Contents x
      FirstDiscreteByIndex
      • 1 Minute to read
      • Print
      • Dark
        Light
      • PDF
      Contents

      FirstDiscreteByIndex

      • 1 Minute to read
      • Print
      • Dark
        Light
      • PDF
      Article summary

      General: FirstDiscreteByIndex

      Creates a flag field where the first occurrence of each unique value in the specified field is set to 1 – but derived as if the table is in order of the sort field

      Purpose

      Use this function to:
      • Sort Data by a specified field before flagging the first unique value
      • Flag earliest record

      Return Value

      PropertyValue
      FieldTypeInteger
      FieldSizeLow
      DataTypeDiscrete
      DataSizeByte


      Example Return Value: 0 , 1, null


      Parameters

      ParameterJSONDescription
      Table“targetTable”: “MyTableName”The target table on which the new field will be created
      Filter“dataset”: {DataSet_JSON}Optional.   If a filter is applied, records not in the filter recordset will be returned as null.
      Function"function":""
      Distinct Key“p1”: “”Required.
      Once the data has been “sorted” by the Sort Field, the first occurrence of each unique value of Distinct Key will be flagged.
      Must be a Discrete DataType from the target table - will not work with continuous fields.  

      Continuous Fields

      If a continuous field needs to be used, it may be possible to derive a discrete field from the continuous field using the CONCAT function.   It may be necessary to apply a filter to reduce the number of discrete values. 

      Sort Field“p2”:Required.
      Field used to determine the sort order of the Recordset.    The first occurrence of Distinct Key in the sorted Recordset will be flagged.
      Must be a discrete DataType from the target table. Will not work with continuous fields.

      JSON Sample

      {
  "method": "BuildBakedField",
  "project": "D3",
  "targetTable": "transactions",
  "overwrite": true,
  "name": "FD_Product_ByDate",
  "function": "firstdiscretebyindex",
  "p1": "product",
  "p2": "date"
}

      Usage Notes

      If no filter is applied, the number of “1’s” in FirstDiscreteByIndex will match the number of unique values in the Recordset for the specified DistinctKey. (By default the Recordset is all records in the target table) 

      See Also:   

      • Engineering | Expression Field
      • FirstDiscrete  (General Functions)

      Example

      ExampleDetails
      DescriptionFlag earliest product record for year 2020
      Input
      • Table: = [transactions]
      • Filter: = {[transactions].[year]=2020} 
      • Function: = FirstDiscreteByIndex
      • Distinct Key:= product
      • Sort Field:= date
      • Name: =FD_Product_ByDate_2020
      Sample




      Was this article helpful?
      What's Next