5 Replies Latest reply: Sep 11, 2018 12:22 PM by Gysbert Wassenaar RSS

    Show 'Is First Order' in Table

    Justin Dallas

      Hello Folks,

       

      I have data that is composed of Orders and Invoices.  The Orders are "children" of the Invoices, and what I'd like to do is mark the first child Order of every Invoice.

       

      My test data looks like so:

       

      Invoice:
      LOAD [Invoice Number] AS '%invoice_number',
      *
      ;
      LOAD * Inline
      [
      'Invoice Number', 'Invoice Total'
          'ABC', 300
          'DEF', 400
      ]
      ;
      
      
      Orders:
      LOAD * Inline
      [
      '%invoice_number','Order Number'
          'ABC','1',
          'ABC','2',
          'ABC','3',
          'DEF','8',
          'DEF','9',
          'DEF','10',    
      ]
      ;
      
      
      EXIT Script
      ;
      

      I'm looking to get something like this.

       

      IsFirstOrder.PNG

       

      The biggest problem I can't get over in my mental model is that I need the row to ignore the current Order Number, yet keep the set analysis restricted to the Invoice Numbe.r

       

      I've tried something the following with no luck.

       

      SUM({<[Order Number]={"=$(Min([Order Number])"}>} [Invoice Total])

       

      Any help is greatly appreciated.