1 of 1 people found this helpful
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.
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]
[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.
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.