2 Replies Latest reply on Oct 11, 2019 8:28 AM by Joshua Milligan

    Working with split columns

    Mikhail Shevchenko

      Hey,

       

      I have a data by project which includes:

      - Assets name used (5 in one cell separated by "|")

      - Total budget per project

      - Total value per project

       

      Example:

       

      Project name          Assets used            Total budget       Total Value

      Alpha                       A | B | C | D               $100                  $900

      Betta                        B | F | S | W              $400                  $800

      Gamma                   A | F  | S | T               $250                  $700

      Delta                       C | D | T | X               $300                   $900

       

       

      To oversimplify, I would like to create scatter plot with Value being an X-axis and Budget being Y-axis where dots would represent individual assets to identify which assets contribute to highest Value projects with lowest Budget. Each asset within the project will be tagged with total amount of the project - which is OK.

       

      First, I've did custom split on Asset name into 5 individual columns. But I can't figure out on how I can apply all of them in one graph. Individually - no problem. But when I drag 5 fields into one table, it doesn't act right.

       

      My question is whether there is a way to do it without changing Excel file as there are other fields such as types of work it relates to (4 in one cell separated by "|") following similar principles and would also require a separate analysis.

        • 1. Re: Working with split columns
          Branden Kornell

          In this case, you want to split the string and then pivot the data, so that each of your assets is on rows rather than columns. Tableau works better with data that's 'tall' rather than 'wide'.

           

          Unfortunately, in Tableau's data editor, you can split strings, but you can't then pivot on the calculations. You might be able to do this in Tableau Prep.

           

          If you don't want to modify the original Excel file, the other option would be to join to a dummy file with X columns with the numbers 1, 2, 3, 4, etc. (up to the max number of assets). Pivot on that (so that each row becomes 4, with a 1, 2, 3, or 4 in it). Then use the number to extract the appropriate asset from the concatenated string.

          • 2. Re: Working with split columns
            Joshua Milligan

            Hi Mikhail,

             

            As Branden mentioned, splitting and pivoting would be a great approach.  Tableau Prep could indeed do this and if it is an option, I would highly consider it.

             

            The suggestion of a dummy file is great, though I would probably just start with everything as rows and avoid having to pivot.  Then you can join those together.  So it would be something like this:

             

            Original Data:

             

            Asset Rows:

             

            Do a cross database join on a calculated join of 1 = 1 so that every Project now has 4 rows:

             

             

            Then, you can do the split and then return the correct Asset based on the row of data:

             

            As you have extra rows now, you'll also have to deal with the fact that that inflates the value of your Budget and Value values.  One possibility is to use a FIXED LOD expression like:

             

            That should get you what you described, though I'm aware you said it was "oversimplified" so there may be some complications to the answer based on specific requirements.

             

            Hope that helps!

            Joshua