adding view
Change-Id: I478c6c6c4082a219c590ddf2b61064fb1470074a
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]
+ }
+
+}
