Sunday, March 26, 2017

Derived Fields in Wave Dataflows using case statements- sample syntax to use

The following dataflow loads an existing dataset, adds derived fields thru a computeExpression node and merges with the source node, then registers the result in a new dataset. notice the combination of condition statements used and syntax.

{

 "z-1": {
    "action": "edgemart",
    "parameters": {
      "alias": "Leads5"
    }
  },
  "z-2": {
    "action": "computeExpression",
    "parameters": {
      "source": "z-1",
      "mergeWithSource": true,
      "computedFields": [
        {
          "name": "CaseStatus",
          "type": "Text",
          "label": "Case Status",
          "saqlExpression": " case  when 'Status' == \"Open\" && 'Include_Flag' == 1 then \"Open\" when 'Status' == \"Closed\"  then \"Closed\"   else \"Other\"  end"
        },
        {
          "name": "CustomOrderedTimeBucket",
          "type": "Text",
          "saqlExpression": " case  when 'Elapsed_Days' == \"NOACTVTY\" then \"0. NOACTVTY\"  when 'Elapsed_Days' == \"GT12MO\" then \"1. >12Mon\" when 'Elapsed_Days' == \"6to12MO\" then \"2. 6to12Mon\" when 'Elapsed_Days' == \"2to6MO\" then \"3. 2to6Mon\"   when 'Elapsed_Days' == \"1MO\" then \"4. 1Mon\" else \"5. Other\" end"
        },
        {
          "name": "CaseAge",
          "label": "CaseAge",
          "type": "Text",
          "saqlExpression": " case  when date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [dateRange([2016,1,1], [2016,12,31])]  then \"2016 Bucket\"  else \"Older\"  end "
        }
      ]
    }
  },
  "z-3": {
    "action": "sfdcRegister",
    "parameters": {
      "alias": "finalZdataset",
      "name": "finalZdataset",
      "source": "z-2"
    }
  }
}






No comments:

Post a Comment