10 Replies Latest reply on Sep 18, 2014 7:16 AM by Jonathan Drummey

    How do you find max row number among line items of a single product ID?

    Rohit Khullar

      First, to introduce my data set. For customers with a given ID, there are multiple line items representing unique transactions. These transactions are identified by Row Number, ordered by time of transaction. The first row number for each customer is 1.


      My goal: for a given historical date, I want to filter the topmost transaction row that occurs before the given date for each customer.


      Problem I am having: I do not know how to create a calculated field MaxRowNumber that processes each row number by customer ID and returns the maximum.


      Example: Say I have this data set and I want to look at 6/10/2014. End result I want is an extraction of all bolded+underlined line items.

      Customer IDRow NumberTransaction Date...

      A

      16/1/2014
      A26/5/2014
      A36/12/2014
      B16/10/2014
      B26/10/2014
      C15/25/2014
      C26/1/2014
      C36/1/2014
      C46/12/2014
      D16/12/2014

       

      Intermediate Step: All transactions before 6/10/2014 (inclusive)

      Customer IDRow NumberTransaction Date...

      A

      16/1/2014
      A26/5/2014
      B16/10/2014
      B26/10/2014
      C15/25/2014
      C26/1/2014
      C36/1/2014

       

      Final Result: Extraction of line items where row number = MaxRowNumber (or similar process for transaction date) for a given Customer ID that occurs before 6/10/2014 (inclusive)

       

      Customer IDRow NumberTransaction Date...
      A26/5/2014
      B26/10/2014
      C36/1/2014

       

      I am able to get to the intermediate step using a series of calculated fields. I don't know how to calculate MaxRowNumber as described above.

       

      If you could help me with this, I would be most appreciative.

       

      Thanks,

      Rohit