topic Recursive sum base on key. in QlikView

Recursive sum base on key.

Mon, 26 Jan 2026 18:19:17 GMT

Actually my scenario is like that:

i have one table like this:

Date , field1 , filed2 , field 3, count

02-12-2012 , A , B ,C , 100

02-12-2012 , X , Y ,Z , 200

03-12-2012 , A , B ,C , 300

03-12-2012 , M , N ,P , 400

04-12-2012 , A , B ,C , 500

04-12-2012 , X , Y ,Z , 200

02-12-2012 , M , N ,P , 600

Now i want to update this table by recursive sum base on key.

key = field1+field2+filed3.

If same key is coming further down for any date , it all add old count with new count and replace the new count. we can understand by this example:

Lets say in this above table, take 1st row so key ll be ABC and for this key count ll be 100.

Same key ABC is coming on date 03-12-2012 with new count 300. Now we ll add this new count to old count and replace it .

so now this row ll be

03-12-2012 , A , B ,C , 400(300+100)

again this same key is coming on date 04-12-2012,

after replacing this row ll be le

04-12-2012 , A , B ,C , 900(500+400)

Now there is no data for ABC key, so it ll go back and new key ll be XYZ.

we ll do same process for this key also.

In the same way we ll procees each key and update the table.

Final table ll be:

Date , field1 , filed2 , field 3, count

02-12-2012 , A , B ,C , 100

02-12-2012 , X , Y ,Z , 200

03-12-2012 , A , B ,C , 400(300+100)

03-12-2012 , M , N ,P , 400

04-12-2012 , A , B ,C , 900( 500+300+100)

04-12-2012 , X , Y ,Z , 400(200+200)

02-12-2012 , M , N ,P , 10000(600+400)

Re: Recursive sum base on key.

Fri, 14 Dec 2012 05:35:43 GMT

Hello,

Is it possible to do this in qlikView?

Nobody can help me ?

Regards

Rahul

Re: Recursive sum base on key.

sujeetsingh — Fri, 14 Dec 2012 06:19:22 GMT

It is not Clear???????

Re: Recursive sum base on key.

flipside — Fri, 14 Dec 2012 14:39:28 GMT

This should do the trick in script ...

Data:

LOAD RowNo() as rowIndx, * inline [

Date , field1 , filed2 , field 3, count

02-12-2012 , A , B ,C , 100

02-12-2012 , X , Y ,Z , 200

03-12-2012 , A , B ,C , 300

03-12-2012 , M , N ,P , 400

04-12-2012 , A , B ,C , 500

04-12-2012 , X , Y ,Z , 200

02-12-2012 , M , N ,P , 600];

left join (Data)

Load

field1 & filed2 & [field 3] as Key,

if(field1 & filed2 & [field 3]=peek(Key),count+peek(RunningCount),count) as RunningCount,

rowIndx

resident Data

order by field1, filed2, [field 3], rowIndx;

flipside

Re: Recursive sum base on key.

Mon, 17 Dec 2012 07:31:23 GMT

Thank you very much flipside.

This is exactly what i want.

Thanks alot.