4 Replies Latest reply on Jun 27, 2016 6:21 AM by gunay.anach.0

    Blend data between 3 tables on Calculated field which results won't appear on all 3 tables.

    gunay.anach.0

      Hello Guys,

      It must be misleading title, but could not come up with better short explanation.

       

      I am dealing with 3 csv files which has common relationship studio names and dates, with 32 studio names spread between these 3 tables.

      And there is no table where I can find all 32 of them in one place to rely on left join which blending allows (As blending won't allow me to do OUTER Join, to able to capture missing studio's from source)

       

      My aim is to create one filter which will filter by studio name with blending studio name calculated field, and will filter across all 3 files only related data to these studios.

      What I've got so far is: If studio "BBC" won't appear in File2.csv it won't be added to the dashboard with data from File1.csv and File3.csv.

       

      Studio fields looks something like this for the 3 sources:

      And I do Calculated Field for each source, due to different studio name conventions in the field and they need to be unified for the further blending:

      Formula

      IF CONTAINS([Studio Name],"20th Century Fox")

      THEN "20th Century Fox"

       

      ELSEIF CONTAINS([Studio Name],"FOX")

      THEN "20th Century Fox"

      ELSEIF CONTAINS([Studio Name],"ABC Studios")
      THEN "ABC Studios"

      ELSEIF CONTAINS([Studio Name],"Arrow Films")
      THEN"Arrow Films"

      ELSEIF CONTAINS([Studio Name],"BBC")
      THEN "BBC"

      ELSEIF CONTAINS([Studio Name],"Channel 4")
      THEN "Channel 4"

      ELSEIF CONTAINS([Studio Name],"Chelsea Films")
      Then "Chelsea Films"

      ELSEIF CONTAINS([Studio Name],"Curzon Artificial Eye")
      Then "Curzon Artificial Eye"

      ELSEIF CONTAINS([Studio Name],"Digital Theatre")
      THEN "Digital Theatre"

      ELSEIF CONTAINS([Studio Name],"Disney")
      THEN "Disney"

      ELSEIF CONTAINS([Studio Name],"EONE")
      THEN "eOne Entertainment"

      ELSEIF CONTAINS([Studio Name], "eOne Entertainment")
      THEN "eOne Entertainment"

      ELSEIF CONTAINS([Studio Name],"Eagle Vision")
      THEN "Eagle Vision"

      ELSEIF CONTAINS([Studio Name],"Entertainment Film Distributors")
      THEN "Entertainment Film Distributors"

      ELSEIF CONTAINS([Studio Name],"HBO")
      THEN "HBO"

      ........

      END

      Domain (20 of 32 members)

       

      Null

      20th Century Fox

      ABC Studios

      Arrow Films

      BBC

      Channel 4

      Chelsea Films

      Curzon Artificial Eye

      Digital Theatre

      Disney

      Eagle Vision

      Entertainment Film Distributors

      HBO

      ITV

      Icon

      Lionsgate

      MGM

      Metrodome

      Movie Partnership

      ...

       

       

      I  end up with this view for studious where StudioNameFieldCalculated won't blend correctly.

       

      Hope i able to explain, any suggestions?