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

    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.
              sujeet singh

              It is not Clear???????

              • Re: Recursive sum base on key.
                Dave Riley

                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