topic Re: Getting the previous row and calculate. in QlikView

Getting the previous row and calculate.

Fri, 19 Oct 2012 11:36:14 GMT

Hello and Good Day, I'm currently new in QlikView and I say this is one powerful B.I Tool.

I'm still learning and I need to solve something..

I have a table

D	ID	Value	Value1
D1	1	2000	0
D2	1	3000	1000
D3	2	2000	0
D4	2	1500	-500
D5	2	500	-1000
D6	3	1000	..

I need to compute for Value1 depending on the ID.

The computation is d2-d1, d4-d3, d5-d4

I find it hard to explain, but I just need to compute for Value1 depending on the next Value.

Thank you and Good Day.

P.S This is for scripting Thank you.

Re: Getting the previous row and calculate.

christophebrault — Fri, 19 Oct 2012 11:39:50 GMT

Hi,

I d'ont really understand your need.

How are your data when you load it, and what do you expect to do in the script exactly ?

Re: Getting the previous row and calculate.

Fri, 19 Oct 2012 11:43:29 GMT

it is from excel file. i loaded it.. and it has a lot of sheets.. so there is a for loop inside..

I have a field I need to compute. As I have said above, the results should be like this:

ID Value Value1

1 1000 0

1 2000 1000

1 500 -1500

2 100 0

2 200 100

Re: Getting the previous row and calculate.

perumal_41 — Fri, 19 Oct 2012 12:07:22 GMT

Hi,

Try this expression in Value

above([Value])-[Value]

use this expression ,u get result

Re: Getting the previous row and calculate.

Fri, 19 Oct 2012 12:11:35 GMT

Hi, I will try this in the expression, but I prefer it in the script

Re: Getting the previous row and calculate.

christophebrault — Fri, 19 Oct 2012 12:12:17 GMT

You can use this :

If(Peek(ID)=ID,

Value-Peek(Value),0) as Value1,

Make sure your data are ordered by ID.

Re: Getting the previous row and calculate.

Fri, 19 Oct 2012 12:18:41 GMT

I will try this one, can you give me an example if possible? thank you

Re: Getting the previous row and calculate.

Fri, 19 Oct 2012 13:45:54 GMT

I tried your computation, yes it is working but the first value should be the one with the '-'

Re: Getting the previous row and calculate.

Sat, 20 Oct 2012 14:46:54 GMT

bump.

Re: Getting the previous row and calculate.

Mon, 22 Oct 2012 02:23:54 GMT

bump

Re: Getting the previous row and calculate.

Mon, 22 Oct 2012 03:14:02 GMT

my problem is like this http://community.qlik.com/message/240999

but i need to compute base on the ID

Re: Getting the previous row and calculate.

christophebrault — Mon, 22 Oct 2012 09:51:54 GMT

I tried your computation, yes it is working but the first value should be the one with the '-'

I don't understand this requirement. Can you explain more or share data ?

Re: Getting the previous row and calculate.

Tue, 23 Oct 2012 00:09:39 GMT

It seems that my data peeks in other rows, i don't know why it works like that.

attached file is what i'm testing..

Re: Getting the previous row and calculate.

Tue, 23 Oct 2012 05:39:40 GMT

Hi,

use Previous() to access the last row.

LOAD

ID,

Value,

Value - Previous( Value ) as Value1

FROM

Source

ORDER BY

Re: Getting the previous row and calculate.

adhudson — Tue, 23 Oct 2012 05:49:16 GMT

Hi,

There are several functions in qlikview to get the values from the previously loaded rows.

Try out Previous and peek functions. you can find beautiful descriptions from QlikView Help Menu or Press F1.

Hope this helps.

Regards

Andrew Hudson

Re: Getting the previous row and calculate.

Tue, 23 Oct 2012 06:10:39 GMT

thank you for replies

alhough i'm still having problem regarding my data.

I think the problem is because there is a date field. I have attached my .qvw, you can check it out.

Thanks

Re: Getting the previous row and calculate.

Wed, 24 Oct 2012 03:18:39 GMT

bump.

Re: Getting the previous row and calculate.

Wed, 24 Oct 2012 05:48:44 GMT

Hello,

from

if([Plate Number] = Peek([Plate Number]), [Odometer Reading] - Peek([Odometer Reading],0)) as peek

why Peek([Odometer Reading],0))?

Do you really want substract the first value from the table in every row?

left join(A)
LOAD
    Date,
    Time,
    if([Plate Number] = Previous([Plate Number]), [Odometer Reading] - Previous([Odometer Reading])) as new_peek
Resident
    A
Order by Date, Time;

I have an expample enclosed.

Re: Getting the previous row and calculate.

Wed, 24 Oct 2012 06:46:38 GMT

Wow thank you very much!

I would try to apply it now to my work. Thanks

Re: Getting the previous row and calculate.

christophebrault — Wed, 24 Oct 2012 10:11:38 GMT

edit : didin't see you had a correct answer before...