8 Replies Latest reply: Oct 25, 2017 4:42 PM by Marco Wedel RSS

    Netting

    Xavier Mascarenhas

      I have the following table

       

      Table1:

      ProductAmount
      A-100
      A-200
      A-300
      A30
      A50
      A50
      A150
      A60
      A150
      A200
      B-500
      B20
      B80
      B100
      B500

       

       

      I need the following output from this table

      Output Needed:

      ProductAmount
      A30
      A60
      B20
      B80
      B100

       

       

      Logic: For each unique product (A or B) I want to eliminate all amounts that sum up to 0(Zero). I want to only keep those amount fields in the data which do not sum up to 0. This is called netting in finance

       

      Any idea how to get that either in the script or through pivot? (ill prefer script)