8 Replies Latest reply on Jun 8, 2018 6:43 AM by Jeffrey Taylor

    Need to treat matches from two fields as the same

    Jeffrey Taylor

      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:

      1. Determine how many "Tickets" are linked to each "Article" through explicit linking (real linking)
        • I can do this now, no problem.
      2. 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.
      3. 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,

      ~JMT