3 Replies Latest reply on Dec 27, 2018 11:58 AM by Jim Dehner

    Excluding relational data

    Kerry Page

      Hey everybody and happy holidays!  I can't wrap my head around this one and need some help. I suspect it's something super easy but I just can not figure it out.

       

      I have data that has a hierarchical relationship, specifically "parent" and "children" relationships. A meter can be a parent or a child, which is determined by the field "parent meter".  I have a separate column set up called Hierarchy that is a calculation that looks to see if the parent meter matches the meter - if so it's a "parent" - if not, it's a "child".

       

      Analysis Action: I need to Exclude all Parent Meters who have a Child Meter with an ID type of 2777 or 2799 (even if the parent has another ID type) in my analysis.

       

      So with the data below,

      • Meter# 12111 has an ID type of 2645, but since it has a child meter 2564012 that has an ID of 2799, I need it flagged and excluded from the data.
      • Meter# 564564 has an ID type of 2681, but since it has several child meters with 2777 (231451, 222222, 111111), I need them flagged/excluded from the data.

       

      I have Prep and I have Tableau Desktop, so a solution in either is fine.@

       

      When I set up my data to just exclude ID types of 2777 and 2799, it still gives me the parent meter data with other system types because it doesn't see the parent/child hierarchy.

       

      What's the best way to tackle this?  All data is dummy data, but hopefully this makes sense to someone.  Workbook attached.

       

         

      MeterParent MeterHierarchyID Type
      123456345345Child2645
      546546345345Child2645
      121111121111Parent2645
      2564012121111Child2799
      564564564564Parent2681
      12154561215456Parent2688
      111111564564Child2777
      222222564564Child2777

      231451

      564564Child2777

       

      Thanks!

       

      Robert Crocker

      Shinichiro Murakami