5 Replies Latest reply on Aug 15, 2018 5:26 AM by Michael Gillespie

    Newbie needing some help on parsing and linking data

    Conor McGreevy

      Apologies if this a stupid question but I'm new to tableau and I'm trying to teach myself some basics.

       

      The issue I have is as follows.

       

      I have a large dataset with transactions. Some of the transactions are linked. So for example Transaction 1 is liked to transaction 5 . Unfortunately the IT team embedded the linking ID in a comments field which I have had to parse - this bit is fine. Only some records contain the link. What I want to see is the all the transactions linked to the original transactions.  This is what I would normally do in MS access

       

      1) Parse the comments field to give me the list of linked transactions

      2) create a new table based on 1

      3) link the original dataset using the table in point 2

      4) only showing matched records

       

      What's the best way of doing this in tableau.  I'm using tableau desktop 10.5.0

       

      Many thanks

      Conor

        • 1. Re: Newbie needing some help on parsing and linking data
          Michael Gillespie

          Conor, conceptually, what you want to do is this:

           

          1) Create a new calculated field based on the column that contains the Linking ID.  You probably want to use the CONTAINS() function.

          2) Use that new field to determine which rows in the data set are related to each other.

           

          The hard part is figuring out exactly HOW to extract the Linking ID from the field that contains it.  Is it consistently formatted/consistent length/in a consistent place in the field?


          The next hard part is figuring out how to structure the data in the new calculated field for the case where there is NO Linking ID.  What value to do you want to be in the column?  "None" is an acceptable answer, but you have to be careful how you use that field to find the linked rows.  You likely don't want a consistent value like "N/A" or "None" but you might be ok with a real NULL.  or you might want to put a different ID in there that's unique to that row.

          • 2. Re: Newbie needing some help on parsing and linking data
            Conor McGreevy

            Many Thanks Michael,

             

            I got the first part to work by building the query in excel using mid and search. I then rewrote the query in tableau using mid and find. This works and I now have a consistent linking field.  There are a few anomalies but I can ignore these as when I do an inner join back to the same data they won’t match and hence they won’t return any records.

             

            The last bit is the bit I’m struggling with . I have the list of parsed trade ids I’m interested in based on the parsed data. How do I then select the relevant records.

             

            So basically I’m trying to link “ New parsed field “ back to the original dataset and only select those records where the new parsed id =  old transaction ID.

             

            I would normally create a lookup table in ms access based on the new parsed IDS and then select all the records where they match.

             

            Let me know if my issue isn’t clear?

             

            Regards

            Conor

            • 3. Re: Newbie needing some help on parsing and linking data
              Michael Gillespie

              OK - you've created the new field that contains the values you want to use to find related transactions. 

               

              First question: What value is in that column for rows that do NOT have a linking field?  That may be important later: for now I'm going to assume NULL.

               

              What you really want, I suspect, is a nice hierarchy of 2 fields: the field that contains the link ID and the field that identifies a unique transaction (I'm assuming you have one).

               

              You create ad-hoc hierarchies like that in Tableau by left-click-dragging the lower level hierarchy field and dropping it on top of the higher level hierarchy field.  That will get you a drillable hierarchy.

               

              For now, drag your new link ID field onto the Rows shelf; then drag your existing unique transaction ID field onto the Rows shelf, to the right of the link ID field.  What you should see (if we've done this right) is all the individual transactions grouped by the link ID.

               

              Try that and report back!

              • 4. Re: Newbie needing some help on parsing and linking data
                Conor McGreevy

                Hi Michael

                 

                Thanks again for your help. I have the linking ID correctly populated but I’m somewhat confused re your comments on the hierarchy bit.  Where there is no linking ID I leave the field as a null value… Ultimately my lookup table should only contain a list of linked ids where a link exists – otherwise the comments field is blank.

                 

                I do not need any information from the transactions which contain the linking id as I have captured what I need separately and I can link back to these using a different join.

                 

                Let me try and explain the process (in MS Access Speak)

                 

                Original dataset =  Cashflow Data

                 

                 

                1.       Using the Cashflow data create a unique list based on the parsed comments field. I would create a separate lookup table

                 

                2.       Join the unique list back to the Cashflow data and select the linked records. – inner join

                 

                3.       I will then join the original transactions and the new set of transactions  based on a separate ID (counterparty code) via a tool tip

                 

                 

                 

                 

                I could do a very bad workaround which is duplicate the cash flow data and then link the duplicate data set to the original dataset using the parsed field and only select the new transactions. I know this would be terrible from a performance perspective.

                 

                I could also pre-process the data but I would like to try and do this in tableau so that I learn how to use its full capabilities.

                 

                Regards

                Conor

                • 5. Re: Newbie needing some help on parsing and linking data
                  Michael Gillespie

                  OK, that's helping. This would be easier to illustrate with a sanitized data set/sample workbook, if you can manage that.

                   

                  In Tableau, you don't need to create lookup tables for what you're trying to do.  If I can simplify, you want to get the set of rows that have a linking ID, and connect that subset of records to a separate data set based on that linking ID (counterparty data).  Is that correct?

                   

                  If so, you can filter the data set based on the existence of a NULL value in the Link ID column.  There are several ways to do it, but the following works well.

                   

                  Create a new calculated field based on the Link ID field.  The calculation is : ISNULL([LinkID]).

                   

                  This will return a boolean value of True/False.  Drag the Link ID field to the Rows shelf, then drag your new field to the Filters shelf.  When prompted, select "False" (in other words, you're telling Tableau to only display records where the value in the LinkID field is NOT NULL).

                   

                  Now you have your set of data, and you can do a blend to your Counterparty table.

                   

                  Let me know if you need to know the difference between JOIN and BLEND in Tableau-speak.