6 Replies Latest reply on Sep 11, 2016 11:04 PM by Kris Hallis

    Lookup name from secondary data source

    Kris Hallis

      Hi,

      I have a worksheet that displays the record times / distance for various track and field events for each age group.

       

      SR:

      IF [Event Type] = "Track" THEN

      {FIXED [Events], [Age Groups]: MIN([Result])}

      ELSE

      {FIXED  [Events], [Age Groups]: MAX([Result])}

      END

       

      This works fine.

      All data is stored in one data source, apart from student names, which is stored in a secondary source. The sources are linked by student ID.

       

      Now, I want to show the name of the student who achieved that record time.

      I can retrieve the ID easily using the following calculated field:

       

      SR Student ID:

      IIF([SR] = [Result], [ID], null)

       

      However, I cannot retrieve the name using similar logic. It just returns null:

       

      SR Student Name

      IIF(attr([SR]) = attr([Result]), attr([vStudentClasses].[Student Preferred]), null)

       

      I would appreciate any help with this.

      Thank you very much,
      Kris

        • 1. Re: Lookup name from secondary data source
          mortenbodaugaard.jrgensen

          Can you not use Cross-data-source joins for this?

          • 2. Re: Lookup name from secondary data source
            Kris Hallis

            Hi,

            The sources are joined with student [ID].

            The thing is, Tableau says all fields must be aggregate or constant when using fields from multiple data sources.

             

            So while this works fine:

            IIF([SR] = [Result], [ID], null)

             

            I can't use this:

            IIF([SR] = [Result], [vStudentClasses].[Student Preferred], null)


            Note: I have just found IIF(attr([SR]) = attr([Result]), attr([vStudentClasses].[Student Preferred]), null) DOES work if there is only ONE underlying row. Otherwise it results null, not *.

             

             

            • 3. Re: Lookup name from secondary data source
              Kris Hallis

              I can kind of understand why IIF([SR] = [Result], [ID], null) works but IIF(attr([SR]) = attr([Result]), attr([vStudentClasses].[Student Preferred]), null) doesn't (unless there is only one underlying row).

              However, I can't see a way around it, as I have to use an aggregate.

              I tried using an LOD expression to force it to "aggregate" only a single row but all fields in an LOD expression must come from the same data source too...

              • 4. Re: Lookup name from secondary data source
                chris.moore.11

                I may be misunderstanding but...

                 

                1) So you have 2 data sources that you are blending?

                2) Your field  IIF([SR] = [Result], [ID], null) works in the first data source correct?

                3) Your second data source is just meant to bring the names over for the appropriate id?

                 

                Then you can just blend and link based on your calculated field in the first data source matching the id in the second data source.

                 

                Ill try to attach a workbook as an example in a second.

                • 5. Re: Lookup name from secondary data source
                  chris.moore.11

                  Hi here's, a workbook as well as two excel files that i am assuming are similar to your two data sources. Hopefully it helps. I used my times from high school because i couldn't help myself.

                   

                  I created "Record id", a calculated field that is basically yours from above. I just had to wrap it in a fixed lod calc to make sure every row for the event gets that id.

                  Capture3.PNG

                   

                   

                  Then you just have to define the relationship between your second source so that it matches that record id to the id from the second source.

                   

                  Go to data -> edit relationships

                  Capture.PNG

                   

                  Then set the relationship so the record id matches the id.

                  Capture2.PNG

                  1 of 1 people found this helpful
                  • 6. Re: Lookup name from secondary data source
                    Kris Hallis

                    Hi Chris.Moore,

                    Thank you so much for your help. Not bad times either!

                    However, because I already have a relationship between [ID] in both data sources (which I need for other sheets), it wouldn't let me create another.

                    I think I have found a solution that works, though. Most names are now showing up (and the reason for the names that are still missing is, I think, another issue related to file years).

                    I created a new calculated field called [SR Student Filter] with the following code: IIF([SR] = [Result], 1, 0)

                    I then added [SR Student Filter] as a filter to only show values equal to 1.

                    This seems pretty simple, but it seems to have fixed the problem (hopefully).

                    Thanks again!

                    Kris