topic Re: Previous-function in QlikView

Previous-function

lukaspuschner — Mon, 02 Mar 2015 09:31:13 GMT

Hello everyone.

I have the following problem.

I have some fields with null()-values and i want to fill them with previous like this.

if(isnull(A), rangesum(previous(A), previous(B) * - 1), A) as C.

I have to use rangesum because in B can be null()-values too. But thats ok.

The Problem is, if A has two or more times in a row a null()-value, C gets this value too.

Help would be nice,

Lukas

Re: Previous-function

kamalqlik — Mon, 02 Mar 2015 09:47:35 GMT

Hi Lukas,

Please give the dummy format for your data and result which is required by you.

Regards,

Kamal

Re: Previous-function

Gysbert_Wassenaar — Mon, 02 Mar 2015 09:52:15 GMT

Maybe like this:

LOAD

alt(A, Peek(D)) as D

alt(A, rangesum(peek(D), -previous(B)) as C

Re: Previous-function

lukaspuschner — Mon, 02 Mar 2015 10:07:11 GMT

Hello Kamal,

here is some data:

Table1:

Budget, Aufwand

22 1

As Result i want:

Budget Aufwand

22 1

21 0

21 2

19 1

18 0

Re: Previous-function

simondachstr — Mon, 02 Mar 2015 10:09:38 GMT

Are you trying to implement this in the script or in the front end?

Re: Previous-function

lukaspuschner — Mon, 02 Mar 2015 10:13:30 GMT

in the script

Re: Previous-function

timanshu — Mon, 02 Mar 2015 10:22:10 GMT

Hi Lukas,

Could you tell What is problem with the statement you used.

can you give the screenshot of what the statement if(isnull(A), rangesum(previous(A), previous(B) * - 1), A) as C. does wrong for you??.

i din't get your this Point:

The Problem is, if A has two or more times in a row a null()-value, C gets this value too.

Re: Previous-function

lukaspuschner — Mon, 02 Mar 2015 11:06:31 GMT

See the example

Re: Previous-function

sunny_talwar — Mon, 02 Mar 2015 11:18:45 GMT

Try this:

Directory;

LOAD if(isnull(A), Peek('A'), A) as A,

FROM

Mappe2.xlsx

(ooxml, embedded labels, table is Tabelle1);

LOAD RangeSum(if(isnull(A), Peek('A'), A), -Peek('B')) as A,

FROM

Mappe2.xlsx

(ooxml, embedded labels, table is Tabelle1);

Please note that Peek function takes the input with single quotes around it.

Let me know this isn't working.

Best,

Re: Previous-function

Gysbert_Wassenaar — Mon, 02 Mar 2015 11:19:38 GMT

Try

Table1:

LOAD alt(A,rangesum(peek(A), -previous(B)),peek(A)) as A, B

FROM ...

See attached qvw.

Re: Previous-function

sunny_talwar — Mon, 02 Mar 2015 11:27:45 GMT

Did not see the example output you gave. This will do the job for you:

LOAD RangeSum(if(isnull(A), Peek('A'), A), -Peek('B')) as A,

FROM

Mappe2.xlsx

(ooxml, embedded labels, table is Tabelle1);

HTH

Best,