
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- new_to_qlikview
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
