4 Replies Latest reply: Aug 3, 2015 8:52 AM by Sasidhar Parupudi RSS

    Aggr function in script

      I have a data and created table to find MAx value using the below expression with Name & Location as Dimension

      Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date)).

       

      Now i need to Bucket each of the name basis the Max Handled and get teh count for each location.

       

       

      NameLocationDateDeptHandled
      RajChennai7/1/2015DO150
      RajChennai7/1/2015DI175
      RajChennai7/2/2015DN200
      RajChennai7/4/2015DO225
      RajChennai7/4/2015DI250
      RajChennai7/7/2015DN275
      RajChennai7/9/2015DO300
      RajChennai7/9/2015DI325
      RajChennai7/14/2015DN350
      SenthilBangalore7/3/2015DO375
      SenthilBangalore7/4/2015DI400
      SenthilBangalore7/6/2015DN425
      SenthilBangalore7/6/2015DO450
      SenthilBangalore7/9/2015DI475
      SenthilBangalore7/11/2015DN500
      SenthilBangalore7/11/2015DO525
      SenthilBangalore7/16/2015DI550
      SenthilBangalore7/5/2015DN575
      Raj 1Chennai7/1/2015DO100
      Raj 1Chennai7/1/2015DI125
      Raj 1Chennai7/2/2015DN150
      Raj 1Chennai7/4/2015DO175
      Raj 1Chennai7/4/2015DI200
      Raj 1Chennai7/7/2015DN225
      Raj 1Chennai7/9/2015DO250
      Raj 1Chennai7/9/2015DI275
      Raj 1Chennai7/14/2015DN300
      Senthil 1Bangalore7/3/2015DO325
      Senthil 1Bangalore7/4/2015DI350
      Senthil 1Bangalore7/6/2015DN375
      Senthil 1Bangalore7/6/2015DO400
      Senthil 1Bangalore7/9/2015DI425
      Senthil 1Bangalore7/11/2015DN450
      Senthil 1Bangalore7/11/2015DO475
      Senthil 1Bangalore7/16/2015DI500
      Senthil 1Bangalore7/5/2015DN525

       

      Agentwise to get Bucketed like this,

       

          

      NameLocationMax Handled in a DayBucket
      RajChennai625500-1000
      Raj 1Chennai525500-1000
      SenthilBangalore10251000-1500
      Senthil 1Bangalore925500-1000

       

      Final output to be as given below.

       

         

      0 -500500- 10001000-1500
      Chennai021
      Bangalore010
        • Re: Aggr function in script
          mayilvahanan ramasamy

          Hi

           

          Try like this

           

          SourceTemp:

          LOAD *, Name&Location&Date As %Key, Name&Location As %NameLocKey INLINE [

              Name, Location, Date, Dept, Handled

              Raj, Chennai, 7/1/2015, DO, 150

              Raj, Chennai, 7/1/2015, DI, 175

              Raj, Chennai, 7/2/2015, DN, 200

              Raj, Chennai, 7/4/2015, DO, 225

              Raj, Chennai, 7/4/2015, DI, 250

              Raj, Chennai, 7/7/2015, DN, 275

              Raj, Chennai, 7/9/2015, DO, 300

              Raj, Chennai, 7/9/2015, DI, 325

              Raj, Chennai, 7/14/2015, DN, 350

              Senthil, Bangalore, 7/3/2015, DO, 375

              Senthil, Bangalore, 7/4/2015, DI, 400

              Senthil, Bangalore, 7/6/2015, DN, 425

              Senthil, Bangalore, 7/6/2015, DO, 450

              Senthil, Bangalore, 7/9/2015, DI, 475

              Senthil, Bangalore, 7/11/2015, DN, 500

              Senthil, Bangalore, 7/11/2015, DO, 525

              Senthil, Bangalore, 7/16/2015, DI, 550

              Senthil, Bangalore, 7/5/2015, DN, 575

              Raj 1, Chennai, 7/1/2015, DO, 100

              Raj 1, Chennai, 7/1/2015, DI, 125

              Raj 1, Chennai, 7/2/2015, DN, 150

              Raj 1, Chennai, 7/4/2015, DO, 175

              Raj 1, Chennai, 7/4/2015, DI, 200

              Raj 1, Chennai, 7/7/2015, DN, 225

              Raj 1, Chennai, 7/9/2015, DO, 250

              Raj 1, Chennai, 7/9/2015, DI, 275

              Raj 1, Chennai, 7/14/2015, DN, 300

              Senthil 1, Bangalore, 7/3/2015, DO, 325

              Senthil 1, Bangalore, 7/4/2015, DI, 350

              Senthil 1, Bangalore, 7/6/2015, DN, 375

              Senthil 1, Bangalore, 7/6/2015, DO, 400

              Senthil 1, Bangalore, 7/9/2015, DI, 425

              Senthil 1, Bangalore, 7/11/2015, DN, 450

              Senthil 1, Bangalore, 7/11/2015, DO, 475

              Senthil 1, Bangalore, 7/16/2015, DI, 500

              Senthil 1, Bangalore, 7/5/2015, DN, 525

          ];

           

           

          SourceTemp1:

          Load %NameLocKey, MaxHandled,

          If(MaxHandled <= 500, '0-500',If(MaxHandled <= 1000, '500-1000', If(MaxHandled <= 1500, '1000-1500'))) As Bucket;

          LOAD %NameLocKey, Max(SumHandled) As MaxHandled Group By %NameLocKey;

          Load %Key, %NameLocKey, Sum(Handled) As SumHandled Resident

          SourceTemp

          Group By %Key, %NameLocKey;

          • Re: Aggr function in script
            mayilvahanan ramasamy

            Hi

             

            And use,

             

            In Pivot table,

            Location and Bucket as dimension

            Count(Bucket) as Measure

             

            Location Bucket 500-10001000-1500
            Bangalore11
            Chennai20