Previous-function

[lukaspuschner]

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

[kamalqlik]

Hi Lukas,

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

Regards,

Kamal

[Gysbert_Wassenaar]

Maybe like this:

LOAD

     alt(A, Peek(D)) as D

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

[lukaspuschner]

Hello Kamal,

here is some data:

Table1:

Budget,      Aufwand

22                1

                    0

                    2

                    1

                    0 

As Result i want:

Budget     Aufwand

22               1

21               0

21               2

19               1

18               0

[simondachstr]

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

[lukaspuschner]

in the script

[timanshu]

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.

[lukaspuschner]

See the example

[sunny_talwar]

Try this:

Directory;

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

     B

FROM

Mappe2.xlsx

(ooxml, embedded labels, table is Tabelle1);

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

     B

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,

S

[Gysbert_Wassenaar · Accepted solution]

Try

Table1:

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

FROM ...

See attached qvw.