5 Replies Latest reply on Jan 23, 2014 6:36 PM by Phillip Burger

    Use value in specific record(s) to set value for all records

    Juanita Heimann

      I'm struggling with doing something that's probably fairly simple.

      1. Set the value of a parameter on the Parameter Control (ex. county_parameter = "Gilliam").

      2. Find the value of another field ("schema") in my primary database for the records where the "county" field = "Gilliam" (ex. schema=3).

      3. Set the value of a third field ("schema2") for ALL RECORDS in the database equal to the value from step 2 above (ex. schema2=3 for all records).

       

      It's steps 2 and 3 that I'm having trouble with. I can't figure out how to reference a specific value from a specific set of records in a calculation that affects all records.

       

      Any suggestions? My primary database looks something like this:

      yearscountyschemaschema2rate
      2005-2009Gilliam310.8
      2006-2010Gilliam310.6
      2007-2011Gilliam39.9
      2005-2009Polk112.9
      2006-2010Polk112.9
      2007-2011Polk112.7
      2005-2009Sherman221.0
      2006-2010Sherman220.0
      2007-2011Sherman218.6
        • 1. Re: Use value in specific record(s) to set value for all records
          Phillip Burger

          Hi, Juanita.  Can you post a workbook?

          • 2. Re: Re: Use value in specific record(s) to set value for all records
            Juanita Heimann

            Here's my workbook. I want to calculate a new field in the database ("schema2") that has the same value as the value of "schema" for the selected county....but for all records, not just for the selected county's records.

             

            Thanks.

            • 3. Re: Re: Use value in specific record(s) to set value for all records
              Phillip Burger

              I see. So, like for when Lane is the selected value for the COUNTIES parameter, you want to see the schema number for Lane, the value is 3, associated with every row in the data? Correct?

               

              I worked on this and couldn't figure a way to do it. I tried LOOKUP()  and some other function but couldn't crack it. I tried treating schema as both a dimension and measure, and as a number and a string (when a dimension). I've tried to solve a problem like this before...it's like trying to do a self join within Tableau.

               

              Workaround: A workaround is to create a second data connection that contains the counties and their schema values. Then do a blend. If it was my data and business problem, and the relationship between county and schema was invariable over time, I'd feel comfortable with this approach until I found a better solution.

               

              If you are using a database and have DDL privileges, create a view that contains one record for each county and two fields for each record, county and schema. Use this as your second data source.

               

              There might be a way to do this using just the existing data connection. I look forward to reading further comments and solutions from other members of the community.

              • 4. Re: Use value in specific record(s) to set value for all records
                Juanita Heimann

                What's step 2 after linking to the second data source? Sorry to ask for the hand-holding. But, I'm brand new at Tableau. I think I did see another thread that described something like this but I can't find it again.

                • 5. Re: Re: Use value in specific record(s) to set value for all records
                  Phillip Burger

                  There are other threads.  There's a lot of content on the forum and it will take a while to get a feel for how it's organized. 

                   

                  To your follow up question, here is a summary of the steps:

                   

                  1) The quickest way to prove this out is for you to create an Excel worksheet, save it, and use it as your second data source. Use two columns. In the first row, enter your column names. In the second row, first column, enter a county name.  In the second row, second column, enter the schema number for the county. The first two rows of your worksheet will look something like table.  The third row here is ready for input of a second county:

                   

                  county
                  county-schema
                  Lane3
                  another county name hereschema number for the county

                   

                  2) Add a second data source to your workbook. This link to discussion about adding other data sources. Tableau does a good job of connecting to an Excel worksheet as you will see.

                   

                  3) Try the data blending. This thread contains useful discussion regarding blending and several links.

                   

                  This is just one approach. Other community members, or more advanced Tableau users than I, might have a different or better solution.