1 Reply Latest reply on May 11, 2018 5:06 PM by swaroop.gantela

    Find first instance of a combination of variables

    Adam Sanders

      Basically I want to set a variable to 0 when a combination of variables is not the first instance


      So for example, data:


      FareWant Fare


      Basically, since the last 2 rows are dupes, I want the fare column to be 0 instead of 100.



      In SQL I would just use a row_number calculation like



      Anyways, thanks for any help!

        • 1. Re: Find first instance of a combination of variables



          Do you have another column that will help distinguish the rows, like a date or a sequence order?

          If so, you can use something like:

          IF [Order]={FIXED [CombinationOriginDestination]:MIN([Order])} THEN [Fare] ELSE 0 END


          where [CombinationOriginDestination] is [Origin]+"|"+[Destination]


          If there isn't an order identifier, you can add a calculation of INDEX() and then use:

          IF [Index]=1 THEN SUM([Fare]) ELSE 0 END


          Please see workbook attached in the Forum thread.