topic Re: Previous Read Row in QlikView

Previous Read Row

marcel_olmo — Sun, 12 Jul 2015 18:39:24 GMT

Hi guys, I've the following example :

Field1	Field2	Result
1	10	1
2	20	2
3	30	3,5
4	40	5,5
5	50	8

Where the field Result needs the value of the previous value of Result itself.

I know that you can get the previous value of Field1 or Field2 because there aren't the same column fields, but when is the same column, it says "error" because Qlik can't find the field "Result" because it doesn't even exist.

Here's the calculation I can't do in Qlik --> Result =(previous(Result)*2 + Field1 )/2

How can you do that?

I attack an excel example of what I mean.

Regards, Marcel.

Re: Previous Read Row

maxgro — Sun, 12 Jul 2015 18:53:33 GMT

LOAD Field1,

Field2,

alt((previous(Result)*2 + Field1 )/2, Field1) as Result

FROM

[Prev Row Example.xlsx]

(ooxml, embedded labels, table is Hoja1);

Re: Previous Read Row

swuehl — Sun, 12 Jul 2015 18:56:31 GMT

PEEK() can do what you want, addressing the output from the row loaded before:

LOAD Field1,

Field2,

Rangesum(Peek(Result)*2, Field1 )/2 AS Result

FROM ....;

edit: Corrected rangesum() arguments separated by comma

Re: Previous Read Row

marcel_olmo — Sun, 12 Jul 2015 19:02:31 GMT

Thanks Massimo for answering so quickly,

the point is that there's no Result column in my database, I have to calculate it from Field1 and Field2. This is an example trying to show what I'm trying to do.

My result field is something like :

if( rowno() =1,1, (previous(NewField) * 2 + Field1)/2 as NewField.

If you do that, Qlik says that "NewField" it doesn't exists.

How can you do that?

Regards, Marcel.

Re: Previous Read Row

marcel_olmo — Sun, 12 Jul 2015 19:04:02 GMT

Thanks swuehl,

I've just wrote the point of that in the answer before. There's no such Result field in the database, that's why Qlik doesn't let me do that.

I've attached what I mean.

Regards, Marcel.

Re: Previous Read Row

swuehl — Sun, 12 Jul 2015 19:10:15 GMT

Have you even tried the script I've posted?

I am not loading the Result column, but calculating it from Field1 and the previous output table row only.

I do get Result 0.5 off, but that's what your expression returns.

edit: emphasized that peek() returns previous output table row and uploaded sample QVW

Re: Previous Read Row

antoniotiman — Sun, 12 Jul 2015 19:36:50 GMT

Hi Marcel,

simply You add

If(RowNo() > 1,Swuel' expression,1) as Result

Regards

Re: Previous Read Row

marcel_olmo — Sun, 12 Jul 2015 22:07:12 GMT

Thanks swuehl,

I've got the issue there. If you use the function Previous() it says the field doesn't exist, but not the function peek().

Thanks a lot for your useful help.

Regards, Marcel.

Re: Previous Read Row

maxgro — Mon, 13 Jul 2015 04:36:09 GMT

LOAD Field1,

Field2,

alt((peek(Result2)*2 + Field1 )/2, Field1) as Result2

FROM

[Prev Row Example.xlsx]

(ooxml, embedded labels, table is Hoja1);