topic Re: Cumulative Sum in Load in QlikView

Cumulative Sum in Load

Thu, 21 Jul 2011 11:28:58 GMT

I need to do a cumulative sum in the resident table as follows

tab1

a, b, c, d

a, 201101, 1, 0

a, 201101, 1, 1

a, 201102, 1, 1

b, 201101, 1, 0

b, 201102, 1, 1

c, 201101, 1, 0

c, 201102, 1, 0

result

a, b, c, d, e, f

a, 201101, 1, 0, 1, 0

a, 201101, 1, 1, 2, 1

a, 201102, 1, 1, 3, 2

b, 201101, 1, 0, 1, 0

b, 201102, 1, 1, 2, 1

c, 201101, 1, 0, 1, 0

c, 201102, 1, 0, 2, 0

swuehl — Thu, 21 Jul 2011 11:50:32 GMT

Ri Robson,

I think it could look like this

Load *,

if(peek(a) =a, peek(e)+c,c) as e,

if(peek(a) =a, peek(f)+d,d) as f

resident tab1 order by a ASC, b ASC;

i.e. order your Input table by a, b ascending (like in your example, but force it).

Check if first column value is identical to previous value, then sum up, else (first appearance of value) start with initial value.

I hope I understood your requirement,

Stefan

Thu, 21 Jul 2011 12:57:47 GMT

Hi Robson,

You can see the attached example.

I hope is what you want.

Best regards.

Thu, 21 Jul 2011 16:47:19 GMT

Thanks guys!