1 Reply Latest reply on Jun 6, 2018 5:21 PM by swaroop.gantela

    Assign value to next row if first value is null

    holly.brooker

      Hi,

       

      I have created a calculated field that assigns the number 1 to the first time point of an ID - If [Entrytime] = Min [time] then 1 else 0 end

      however if the Customer field is null I want to assign the next value as 1 until it finds a customer value?

       

      There can be multiple rows per ID, so for instance if there are 5 nulls before there is a customer I would like them all to be ranked 1.

       

      I have tried using if [Entrytime] = Min [time] -1 but as they are date fields it is changing the time not returning the next row.

       

      Thanks!

        • 1. Re: Assign value to next row if first value is null
          swaroop.gantela

          Holly,

           

          I'm not sure if I caught the gist, but please see if the workbook

          attached in the Forum Thread could give some ideas.

           

          If the goal is mark all initial Customer Nulls with a 1

          and the very first nonNull customer, then you can use

          IF FIRST()=0

          OR ATTR([Customer])="Null"

          OR ATTR([Customer])<>"Null" AND LOOKUP(ATTR([Customer]),-1)="Null" //currently not Null, but previous one was Null

          THEN 1

          ELSE 0

          END

           

          This will require ID, Customer, and Time to be somewhere on the shelves

          so that the "Compute Using" can be properly set to mark the first, do the lookup, and restart every ID.

          Is your eventual sheet going to be a table or a graph?

           

          If your goal was to mark them differently (first nonNull customer only if not preceded by Nulls, etc.)

          please mark in the attached excel your desired output.