4 Replies Latest reply: Dec 17, 2012 2:31 AM by Rahul Rao

# Recursive sum base on key.

Hi

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.

Hello,

Is it possible to do this in qlikView?

Nobody can help me ?

Regards

Rahul

• ###### Re: Recursive sum base on key.

It is not Clear???????

• ###### Re: Recursive sum base on key.

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)

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.

Thank  you very much flipside.

This is exactly what i want.

Thanks alot.