4 Replies Latest reply: Apr 28, 2012 2:53 PM by supreeth RSS

    SUBTRACTION OF VALUES IN ONE FIELD

      4
      3
      4
      3
      21
      3244

       

      Hi all please help me out on this i wanna result like 

      -1
      1
      -1
      18
      3223
      -3244

       

      for example  3-4 =-1


      please help me the data is not not only 6 values.. i want a general method where applied i can get computed subtracted column.. and I have only one field with many values..... so plz any one help me out please

        • SUBTRACTION OF VALUES IN ONE FIELD
          Stefan Wühl

          You can probably do it using peek() function to retrieve previous line record and doing a simple subtraction.

           

          Maybe like this:

           

          INPUT:

          LOAD recno() as LINE, FIELD INLINE [

          FIELD

          4

          3

          4

          3

          21

          3244

          ];

           

          RESULT:

          LOAD LINE,

          rangesum(peek(FIELD),-FIELD) as NEWFIELD,

          FIELD

          Resident INPUT order by LINE desc;

           

          drop table INPUT;

            • SUBTRACTION OF VALUES IN ONE FIELD

              Hi thanks for the suggestion but the problem is i have one field called SUM in this column u I HAVE MANY ROWS..

              WHAT I'M TRYING IS I HAVE TO GET DIFFERENCE OF SECOND ROW  WITH FIRST ROW SIMILARLY 3RD ROW WITH 2ND ROW...... THIS SHOULD HAPPEN FOR THE ENTIRE COLUMN AND FOR ALL DATA PRESENT.... I GUESS I HAVE EXPLAINED MY DOUBT CLEARLY.......... IN ABOVE EXAMPLE I HAVE GIVEN EXAMPLE .....

              I'M FINDING IT DIFFICULT TO SUBTRACT SINCE I HAVE ONE COLUMN WITH DATA... HOPE I'M CLEAR THIS TIME PLEASE HELP ME ON THIS swuehl

                • SUBTRACTION OF VALUES IN ONE FIELD
                  Stefan Wühl

                  supreeth,

                   

                  I believe I already gave you an answer. My bottom line is, look into the inter record functions, look into peek() function  if you want to calculate your new values in the script (what I have assumed), or above() if you want to do similar in a table chart.

                   

                  If you run my above script, you should get the exact result you stated in your original point, so I think I must miss your pouint here.

                   

                  if you use

                  LOAD

                  ...

                  FIELD-peek(FIELD) as NEWFIELD

                  ...

                  from TABLE;

                   

                  this should

                  GET DIFFERENCE OF SECOND ROW  WITH FIRST ROW SIMILARLY 3RD ROW WITH 2ND ROW...... THIS SHOULD HAPPEN FOR THE ENTIRE COLUMN AND FOR ALL DATA PRESENT....

                   

                  In my sample, I used a slightly different code to handle the situation for the very last line, you want to see the negative value of the last FIELD value as NEWVALUE, like there would be a virtual additional line with zero value.

                   

                  Well, I don't have much more time to spend on this unless you clarify what is wrong with my above code and what you expect instead (e.g. if you don't like the additional LINE field, just drop it. Maybe do a resident load with table ordered by LINE asc first to get inline with your original layout, but I assume and believe this shouldn't matter anyway)

                   

                  Regards,

                  Stefan