4 Replies Latest reply: Aug 22, 2013 3:49 AM by IOSU GARCIA RSS

    Row wise calculations

    Abhijit Bansode

      I have a below data table:

       

      TypeValue
      Room Expenditure700
      Telephone Expenditure500
      Room Profit400
      Telephone Profit100

       

      I want expected table as below:

       

      TypeValue
      Room Expenditure700
      Telephone Expenditure500
      Room Cost300
      Telephone Cost400
      Room Profit400
      Telephone Profit100

       

      Room Cost = Room Expenditure- Room Profit

      same for telephone and other Types present in the table.

       

      How can I calculate and generate these new rows in script?

       

      Any help greatly appreciated.

        • Re: Row wise calculations

          Hi Abhijit,

           

          Do you have any field to group the different types? Or just have to compare each name?

           

          Are you sure that this output wouldn´t be better for your document? (All the fact tables in the same row)

           

          TypeCostExpenditureProfit
          Room200350150
          Telephone300440140
          • Re: Row wise calculations
            Gysbert Wassenaar

            Try:

             

            T1:

            LOAD * INLINE [

                Type, Value

                Room Expenditure, 700

                Telephone Expenditure, 500

                Room Profit, 400

                Telephone Profit, 100

            ];

             

            load 'Room Cost' as Type,

            sum(if(Type='Room Expenditure', Value, if(Type='Room Profit',-Value,0 ))) as Value

            resident T1;

             

            load 'Telephone Cost' as Type,

            sum(if(Type='Telephone Expenditure', Value, if(Type='Telephone Profit',-Value,0 ))) as Value

            resident T1;

             

            See attached qvw

            • Re: Row wise calculations

              If you don't have any field to group all the different types, you need one. Try creating a new field with this:

               

              SubField(Type, ' ', 1) as NewType,     // For differenciating Telephone, room,...

              SubField(Type, ' ', 2) as DataType     // For differenciating Expenses, cost...

               

              Once you have this new fields, it will be easy to achieve what you want, just following what Wassennar posted.