6 Replies Latest reply on Jan 20, 2017 10:13 AM by Jamieson Christian

    LINKING 2 FIELDS IN SINGLE DATA SOURCE

    Trevor Corbin

      I have two tables/views (OLD and NEW) joined by a common dimension that is static.  In the example provided, the join field is location. I want to compare Old and New measures across other dimensions in the data, but since the rows have changed, I can't use the Old_dimension field OR the New_dimension field, as it would not sum the measures properly.

       

      Instead, I think I need to create some new calculated fields to achieve the desired result (table immediately below). Any ideas?

       

      DepartmentNew BudgetOld BudgetDifference
      HR$100,000$150,000-$50,000
      Sales$300,000$300,000$0
      Analytics$200,000$200,000$0
      Marketing$300,000$300,000$0
      Ops$400,000$350,000$50,000
      Partnerships$500,000$500,000$0

       

      The sample data is pasted below and attached in a Tableau Workbook v10.1.

       

      LocationNew CategoryNew Direct ReportNew BudgetOld CategoryOld Direct ReportOld Budget
      1HREVP$100,000MarketingSVP$300,000
      2SalesSVP$150,000PartnershipsSVP$250,000
      3 PartnershipsSVP$250,000
      4AnalyticsEVP$200,000SalesSVP$75,000
      5MarketingSVP$300,000SalesSVP$75,000
      6SalesSVP$150,000HREVP$150,000
      7 SalesSVP$75,000
      8OpsEVP$400,000OpsEVP$350,000
      9PartnershipsSVP$250,000AnalyticsEVP$200,000
      10PartnershipsSVP$250,000SalesSVP

      $75,000

        • 1. Re: LINKING 2 FIELDS IN SINGLE DATA SOURCE
          Jamieson Christian

          Trevor,

           

          The most convenient approach is to pivot your data. You really want Old and New as separate entries, to support the type of reporting you are doing. See the attached workbook (v 10.1.3).

           

          Unfortunately, because there are multiple "old" and "new" fields, you can't use Tableau's convenient Pivot feature outright. You'll have to get a little more elaborate.

           

          1. On the Data Source tab, create a union of the "sample" sheet. So you will have two copies of the data.

           

          2. Create a calculated field [Adj Table Name] to turn the auto-generated union field [Table Name] field into something more self-describing for other calculated fields.

           

          3. Create "old" and "new" calculated fields for each of your dimensions fields: [Category] and [Direct Report].

           

           

          4. Create separate fields [Old Budget calc] and [New Budget calc]. Each field only includes a value if it's in the "old" or "new" entry (so for any given row, one or the other will be blank).

           

           

          5. Create a field [Budget Difference].

           

          6. Now drop [Category], [New Budget calc], [Old Budget calc], and [Budget Difference] on your view. Adjust some aliases for the pretty factor, and viola! (I left the "Null" in there for completeness, but you can filter it out easily.)

           

          I hope this helps!

          1 of 1 people found this helpful
          • 2. Re: LINKING 2 FIELDS IN SINGLE DATA SOURCE
            Jamieson Christian

            Edited to fix a couple issues.

            • 3. Re: LINKING 2 FIELDS IN SINGLE DATA SOURCE
              Trevor Corbin

              That answer makes sense.  Hopefully I didn't oversimplify when I posted.  How does that change if my original data source looks like this:

               

               

              The new (live) data is coming from the SEATMAP table, while the old data is coming from the RenewalUniverse table. The RenewalUniverse table is essentially just a static and abridged version of the joined tables above it.

               

              Thanks

              • 4. Re: LINKING 2 FIELDS IN SINGLE DATA SOURCE
                Jamieson Christian

                Trevor,

                 

                Without seeing specifically what is in each table, I'll hazard a guess that you just need to UNION the SEATMAP and RenewalUniverse tables to achieve the same structure that I did with the Excel sample.

                 

                The goal is to have separate "New" and "Old" records (UNION) rather than to have "new" and "old" values as separate columns (JOIN).

                 

                (Note that if your data source is a database, you'll need to build a Custom Query to perform the union. The ability to use Tableau's Union interface to do database unions is still "coming soon".)

                 

                Hope this helps.

                1 of 1 people found this helpful
                • 5. Re: LINKING 2 FIELDS IN SINGLE DATA SOURCE
                  Trevor Corbin

                  That is what I was thinking - a union on the old and new data. Unfortunately, my data source is a database (SAP Hana). Can you explain or point me to how to create a Custom Query?

                   

                  Thanks!

                  • 6. Re: LINKING 2 FIELDS IN SINGLE DATA SOURCE
                    Jamieson Christian

                    Trevor,

                     

                    DISCLAIMER: I have never used SAP Hana and I don't know to what degree its SQLScript language varies from SQL.

                     

                    Supposedly, SAP Hana's SQLScript is just SQL with extensions, and you probably won't need the extensions.

                    » SAP HANA SQLScript

                     

                    Tableau has an online help article specifically about connecting to SAP Hana. It looks like Custom Query is supported.

                    SAP HANA

                     

                    The simplest form of a union SQL query (assuming both tables are identical) is:

                     

                    SELECT * FROM SEATMAP_COMPREHENSIVE
                    UNION
                    SELECT * FROM RenewalUniverse;

                     

                    But you will need to refine this according to the specifics of your table structures and the data you need to retrieve.

                    1 of 1 people found this helpful