adding view

Change-Id: I478c6c6c4082a219c590ddf2b61064fb1470074a

demo-l4c.view.lkml

diff --git a/demo-l4c.view.lkml b/demo-l4c.view.lkml
new file mode 100644
index 0000000..4ca46a5
--- /dev/null
+++ b/demo-l4c.view.lkml
@@ -0,0 +1,179 @@
+#############################################################################################################
+# Owner: Customer Main
+# Created by: yangstanley@, hillz@
+# Created: April 8, 2021
+# Purpose: This table includes a list of legal entities which could be Association, corporation, partnership,
+#          proprietorship, non-profit institution, government institution, or individual that has legal standing
+#          in the eyes of law. Associated with each other via a legal hierarchy.
+#
+#          This table is partitioned by data injection date(_PARTITIONDATE)
+#############################################################################################################
+
+view: demo_l4c {
+  derived_table: {
+    sql: WITH legal_entities AS (
+          SELECT
+            legal_entity_id
+          , name
+          , address.revision AS revision
+          , address.region_code AS region_code
+          , address.language_code AS language_code
+          , address.postal_code AS postal_code
+          , address.sorting_code AS sorting_code
+          , address.administrative_area AS administrative_area
+          , address.locality AS locality
+          , address.organization AS organization
+          , duns
+          , customer_id
+          , parent_id
+          , commit_timestamp
+          , address_lines
+          , recipients
+          , _PARTITIONDATE AS partition_date
+          FROM concord-prod.service_customermdm_test.legal_entities
+          LEFT JOIN UNNEST(address.address_lines) AS address_lines
+          LEFT JOIN UNNEST(address.recipients) AS recipients
+          WHERE _PARTITIONDATE = (
+                 SELECT MAX(_PARTITIONDATE)
+                 FROM concord-prod.service_customermdm_test.legal_entities
+                 WHERE _PARTITIONDATE > DATE_ADD(CURRENT_DATE, INTERVAL -3 DAY) )
+          AND _PARTITIONDATE > DATE_ADD(CURRENT_DATE, INTERVAL -3 DAY)
+          GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
+          )
+
+          SELECT
+            legal_entity_id
+          , name
+          , revision
+          , region_code
+          , language_code
+          , postal_code
+          , sorting_code
+          , administrative_area
+          , locality
+          , organization
+          , duns
+          , customer_id
+          , parent_id
+          , commit_timestamp
+          , partition_date
+          , STRING_AGG(address_lines, " " ORDER BY address_lines) AS address_lines
+          , STRING_AGG(recipients, " " ORDER BY recipients) AS recipients
+
+          FROM legal_entities
+          GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ;;
+  }
+
+  ########## PRIMARY KEYS ##########
+
+  dimension: id {
+    primary_key: yes
+    group_label: "IDs"
+    label: "Billing Account ID"
+    description: "ID of the legal entity/billing account"
+    type: string
+    sql: ${TABLE}.legal_entity_id ;;
+  }
+
+  ########## DIMENSIONS ##########
+
+  dimension: address_lines {
+    group_label: "Address"
+    description: "Street address of the customer. Some data are not well documented or are missing"
+    type: string
+    sql: ${TABLE}.address_lines ;;
+  }
+
+  dimension: administrative_area {
+    group_label: "Address"
+    description: "State/Province of the legal entity"
+    type: string
+    sql: ${TABLE}.administrative_area ;;
+  }
+
+  dimension_group: commit {
+    hidden: yes
+    description: "The date the legal entity's data was last committed"
+    type: time
+    datatype: datetime
+    sql: ${TABLE}.commit_timestamp ;;
+    timeframes: [date]
+  }
+
+  dimension: customer_id {
+    hidden: yes
+    group_label: "IDs"
+    description: "ID of the customer associates to the legal eneity"
+    type: string
+    sql: ${TABLE}.customer_id ;;
+  }
+
+  dimension: duns_id {
+    group_label: "IDs"
+    label: "D&B DUNS ID"
+    description: "Duns & Bradstreet ID of the legal entity"
+    type: string
+    sql: ${TABLE}.duns ;;
+  }
+
+  dimension: language_code {
+    group_label: "Address"
+    description: "ISO language code of the legal entity"
+    type: string
+    sql: ${TABLE}.language_code ;;
+  }
+
+  dimension: locality {
+    group_label: "Address"
+    description: "City of the legal entity"
+    type: string
+    sql: ${TABLE}.locality ;;
+  }
+
+  dimension: name {
+    label: "Billing Account Name"
+    description: "Name of the legal entity/billing account"
+    type: string
+    sql: ${TABLE}.name ;;
+  }
+
+  dimension: parent_id {
+    group_label: "IDs"
+    label: "Parent Billing Account ID"
+    description: "The ID of the parent company of the legal entity"
+    type: string
+    sql: ${TABLE}.parent_id ;;
+  }
+
+  dimension: postal_code {
+    group_label: "Address"
+    description: "Postal Code of the legal entity"
+    type: string
+    sql: ${TABLE}.postal_code ;;
+  }
+
+  dimension: region_code {
+    group_label: "Address"
+    description: "Country code of the legal entity"
+    type: string
+    sql: ${TABLE}.region_code ;;
+  }
+
+  dimension: sorting_code {
+    group_label: "Address"
+    description: "Last 4 digits of the 9-digit USA postal code of the legal entity"
+    type: string
+    sql: ${TABLE}.sorting_code ;;
+  }
+
+  ########## MEASURES ##########
+
+  measure: count {
+    label: "Billing Account Count"
+    description: "Legal entity count"
+    type: count_distinct
+    sql: ${id} ;;
+    drill_fields: [customer_entities.id ,customer_entities.name, id ,name, duns_id, parent_id]
+  }
+
+}