4 Replies Latest reply: Nov 2, 2017 2:16 PM by Justin Dallas RSS

    Group by in chart

    Justin Dallas

      Hello Everyone,

       

      I have a table that looks like this.

       

      AOW_3.PNG

       

      As you can see, there are two rows caused by the fact that I've concatenated two tables, Revenues and Expenses.  These tables have to be concatenated for Calendar reasons.  If you notice, you'll see that the four columns to the right are the same, but the leftmost ones are different. This is because some of these fields ONLY exist on the initial Revenue table and others exist ONLY on the Expense table before concatenation.

       

      Is there a way I can tell the chart to "combine" both lines?

       

      In SQL, I would do something like SELECT VENDORID, Expense Date, Revenue Date, Document Number, TRXDSCN FROM Blah GROUP BY [Invoice Number].

       

      I feel I have to use AGGR somehow, but it wouldn't be based off a MAX, or MIN that I've seen in examples.

       

      Here is a test script.

      DummyData:
      LOAD * Inline
      [
      VENDORID,Expense Date,Revenue Date,Document Number,TRXDSCRN, Bill Date,Bill To ID,Invoice Number,Order Number
      HARMON,08/29/2017,,248986,248986,09/13/2017,SPECAR01,186329A,186329
      ,,09/13/2017,,,09/13/2017,SPECAR01,186329A,186329
      ]
      ;
      

       

      Any help is greatly appreciated.