3 Replies Latest reply on Mar 17, 2019 3:47 AM by Jim Dehner

    Replace missing values with values of matching row id

    Jody B

      I'm struggling to replace some null values of a row with the values of a matching row. I would prefer to solve the problem by creating a new/replacing a column instead of working with joins, as the data I'm working with is in one file.

       

      Example of the data:

       

      ID     Date                    Number

      1     01-01-2018          10

      2     01-01-2018          50

      2     02-01-2018          50

      3     02-01-2018          30        

      4     02-01-2018          30

      1     02-01-2018          null

      4     02-01-2018          null

       

      Somehow, the 'number' variable does not contain any values for ID1 and ID4 on date '02-01-2018'.

       

      As the 'number' is a constant variable and doesn't change over time, I would like to replace the 'null' values with '10' for ID1 and with '30' for ID4.

       

      What formula should I use?

       

      Thanks in advance.

        • 1. Re: Replace missing values with values of matching row id
          Jim Dehner

          There are a couple thins you could do (don't hold me to the syntax  I don;t have your twbx workbook 

           

          if they are truly always the same you could use a case statement

                    case ID

                    When 1 then 10

                    When 2 then 50

                    When 3 then 30

                    When 4 then 30

                    end

           

           

          A different approach would us a conditional           if               then type statement

                    if ID=1 and isnull(Number) then 10

                    elseif ID=4 and isnull(Number) then 30

                    else Number  end

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Replace missing values with values of matching row id
            Jody B

            Hi Jim,

             

            thank you very much for your reply.

             

            Unfortunately, the case is a bit more difficult. Instead of just the sample I provided, the whole dataset consists of more than thousand rows with more than 500 different ID’s. Therefore, the methods are not useful.

             

            Also, I need to replace the null values of not just one column, but multiple columns with null values.

             

            Do you have a solution? And can you provide a packaged workbook?

             

            thank you in advance

            • 3. Re: Replace missing values with values of matching row id
              Jim Dehner

              Jody

              we can only work with the information you tell us - if you had that many replacements you would have included it in the post and I would have told you to create a separate file with a was/now type replacement to join to you current data

              and NO - we are here to help you solve you problem not to do it for you

              Jim