topic SUBTRACTION OF VALUES IN ONE FIELD in QlikView

SUBTRACTION OF VALUES IN ONE FIELD

Tue, 24 Apr 2012 17:39:47 GMT

3244

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

-1

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

swuehl — Tue, 24 Apr 2012 19:22:40 GMT

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

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

Wed, 25 Apr 2012 16:07:56 GMT

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

swuehl — Wed, 25 Apr 2012 20:32:02 GMT

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

SUBTRACTION OF VALUES IN ONE FIELD

Sat, 28 Apr 2012 18:53:28 GMT

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