Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

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)

1 Solution

Accepted Solutions
flipside
Valued Contributor II

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)

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

4 Replies
Not applicable

Re: Recursive sum base on key.

Hello,

Is it possible to do this in qlikView?

Nobody can help me ?

Regards

Rahul

sujeetsingh
Honored Contributor III

Re: Recursive sum base on key.

It is not Clear???????

flipside
Valued Contributor II

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)

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

Not applicable

Re: Recursive sum base on key.

Thank  you very much flipside.

  This is exactly what i want.

Thanks alot.