SUBTRACTION OF VALUES IN ONE FIELD

[#1]

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

[swuehl]

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;

[#3]

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

[swuehl · Accepted solution]

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

[#5]

Thanks i got the answer i was looking for ..... I used above(column(1)) which gave me one more column and i could do the subtraction.. Anyways thank you Stefan and  swuehl for guiding me