I have what I hope is an interesting problem! I cannot share the data or workbook, unfortunately, but I will try to be explicit here with what I want to accomplish.
I use Tableau 10.1 and have a data set made from a bunch of joins from CSV sources that looks something like this:
[TICKETS table] <--(Full Outer)--> [ARTICLES table]
Join clause is "Article ID"
This allows me to determine which items in the "Tickets" table have a match in the "Articles" table. This is good, and does exactly what I want. The problem I am facing is that a lot of people have bad behavior, and don't establish the link between the "Ticket" and the "Article" properly in our system. Instead, they reference an article ID in a different free-form text field in the "Ticket."
I can extract this easily enough through a REGEXP_EXTRACT calculation, which I have done. So now, I can find out how many distinct "Ticket Numbers" are linked to an "Article ID" via either real linking or implied linking (Article ID exists in a text field in the "Ticket" but the "Ticket Number" is not in the "Articles" table), but not both. I need to treat these as the same so I can report on a total number of links established via either mechanism.
I would like to do the following:
- Determine if the value in the "Implied Article ID" calculated field for a "Ticket" exists as a real "Article ID" occurring anywhere in the "Articles" table.
- If it does, write the value of "Implied Article ID" as the real "Article ID" value for that "Ticket", along with the other related "Article" fields (title, URL, etc.) from the "Articles" table.
- If it does not, leave the "Article ID" value for that "Ticket" as null.
This is necessary because as much as I care about the "Article ID" (a little bit), I also care about other Article information (such as title, etc.), and I need to be able report as if they are the same thing.
This will allow me to do each of the following:
- Determine how many "Tickets" are linked to each "Article" through explicit linking (real linking)
- I can do this now, no problem.
- Determine how many "Tickets" are linked to each "Article" through implicit linking (use of a text field)
- I can do this now, no problem, with some regex.
- Determine how many total "Tickets" are linked to each "Article" regardless of method.
- This is what I cannot figure out how to do.
Is this possible in Tableau?
Thanks in advance,