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:

       

      SRNOriginDestination
      FareWant Fare
      .05AB100100
      .05AB1000
      .05AB1000

       

      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

      ROW_NUMBER() OVER( PARTITION BY  SRN,

      ,Origin,Destination)

      Anyways, thanks for any help!

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

          Adam,

           

          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.