3 Replies Latest reply on Mar 7, 2017 4:28 PM by Alex Marlow

    Calculated Field for NULL Values

    Alex Marlow

      Hi Everyone,


      I am working with government data and need some assistance.


      So the Candidate ID is my primary field; however, in my Independent Expenditure data that is sometimes missing. I have joined two data sources together by Last name, District, and State to try and help set each candidate's ID. This is almost completed, but I think I need a calculated field or some way of grouping the same candidate IDs together.


      Thanks for your help! 

        • 1. Re: Calculated Field for NULL Values
          Norbert Maijoor

          Hi Alex,


          Your challenge is confusing;) What I see when i open your workbook



          Two Datasources:

          a. Candidate Info

          b. Expenditures



          The Datasource Candidate Info consist of 2 tables

          a. Table Candidate Info

          b. Independent Expenditures



          If I look at the structure of the tables Independent Expenditures en Expenditures is identical.









          Without further investigation of the datasource "Candidate info" I assume the join between tabel candidate info and Independent Expenditures is not correct based on Last Name, District and State. I would have expected just to join in Candidate Id


          Hope to hear from you.




          • 2. Re: Calculated Field for NULL Values
            Alex Marlow

            Hi Norbert,


            This is a very tough problem that I have been working on for awhile to solve.


            So to give you a little bit more information, the independent expenditure data is the data that I need to be able to match up with the results10 data connection. Within the results10 data connection, I need to use the 2010 US House and Senate results data. I need to be able to join those two, the problem is that if you look within just the Independent Expenditure data, some candidates do not have their candidate ID listed for every entry. Thus resulting in wrong data when I do a join.


            The data set Candidate Info is all of the candidates from that year and lists all of their candidate IDs. What I would like to do is be able to join Candidate Info and Independent Expenditures to then be able to set all of the missing candidate IDs within Independent Expenditures based off of last name, district, and state.


            Hopefully that gives you a little more of a background.


            If you have any questions, please let me know.


            Thanks for your help!



            • 3. Re: Calculated Field for NULL Values
              Alex Marlow

              Here is a visual representation of what I need to do:


              I need to set the candidate ID to the Can ID where they are null values. then all of the data should be mapped.