3 Replies Latest reply on Jul 29, 2013 6:18 AM by Jonathan Drummey

    Forcing a group of fields to have a specific value?

    alan.balasundaram

      Consider a set of web transaction events:

       

      SessionEventTypeReferrerURL
      90210PageViewwww.tempuri.orgwww.mysite.com
      90210PageViewwww.mysite.comwww.mysite.com/about
      90210PageViewwww.mysite.comwww.mysite.com/coolstorybro
      90210Commentwww.mysite.comwww.mysite.com/coolstorybro
      90211PageViewnullwww.mysite.com
      90212PageViewstallion.tumblr.comwww.mysite.com
      90212Loginwww.mysite.comwww.mysite.com/login

       

      Say I create 2 groups based on the Referrer Data:

       

      External: tempuri.org, stallion.tumblr.com

      Internal: www.mysite.com

       

      I'd like to see the number of events that originated from the External group, regardless of what the actual referrer is. So for example, in the session: 90210, There were 3 pageviews, and 1 comment that originated from an External referrer.

       

      How can I implement this logic: For all rows for a session, if any referrer is in External group, set the rest of the referrers in the session to "External"

       

      I think I can use a calculated field? Get all referrers for a session, if any are in Group External, then set the field to be "True".

       

      I'm very new to tableau, so my terminology is probably off.

        • 1. Re: Forcing a group of fields to have a specific value?
          Dimitri.B

          If I understood the problem correctly: you need to check all rows within each session, and if even one row has an external referrer then flag all rows of that session as external.

          This would require iterating through rows and comparing values, and Tableau doesn't have the machinery to do this. This would be an easy job for SQL, and you can probably use RAW_SQL Tableau functions, but that depends on your data source.

          1 of 1 people found this helpful
          • 2. Re: Forcing a group of fields to have a specific value?
            alan.balasundaram

            Thank you Dimitri.


            Yes you understood the problem. The underlying data source is CSV (so it uses Microsoft's Jet Engine), and I tried RAW SQL, but got syntax errors. I was able to work around it by using your suggestion, however.

             

            I created a 2nd data connection with custom sql:

             

            SELECT [data#csv].[session] as [session]

                         [data#csv].[referrer] as [referrer]

            FROM [data#csv]

            WHERE

                        [referrer] NOT LIKE '%mysite.com%' AND [referrer] IS NOT NULL

             

            This gave me a seconary datasource of all the sessions that map directly to an External referrer. This allowed me to group sessions by External referrer, and thus was able to get the events associated with each external referrer.

             

            Then I needed more memory, so I booted directly into my native Bootcamp partition which caused the Tableau Desktop trial to immediately expire. So... there is that.

             

            But thanks!

            • 3. Re: Re: Forcing a group of fields to have a specific value?
              Jonathan Drummey

              Alan,

               

              You didn't specify what you wanted to do *with* the data, so there are several ways you might go about this. The Custom SQL approach can give you the most flexibility because the internal/external identifier is then part of the data source. Here are a couple of others, using Superstore Sales as an example and Category as the counterpart to Referrer, Category== Telephones and Communication as the counterpart to Internal, and Order as the counterpart to Session. (In Superstore Sales there can be multiple rows within a given order).

               

              - If you are always aggregating across the internal/external distinction (i.e. not having the Referrer in the view, then you can create a calculated field such as: IF SUM(IF [Category] == "Telephones and Communication" THEN 1 ELSE 0 END) > 0 THEN "Telephone" ELSE "Other" END.

               

              - If the you need the Referrer/Category in the view, then you can use a table calculation to generate a flag and propagate that back to every row. There are several ways to do this, one is this calc: TOTAL(MAX(IF [Category] == "Telephones and Communication" THEN "Telephone" ELSE "Other" END)), which has a compute using of Category so it partitions on teh Order ID.

               

              I've set both up in the attached. However, since we're dealing with an aggregate measure or table calc aggregate, those can reduce the options available for further computation based on those results, however not knowing your goals I can't say which of these options might work for you.

               

              Jonathan