Aggr function in script

[#1]

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.

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

Agentwise to get Bucketed like this,

Name	Location	Max Handled in a Day	Bucket
Raj	Chennai	625	500-1000
Raj 1	Chennai	525	500-1000
Senthil	Bangalore	1025	1000-1500
Senthil 1	Bangalore	925	500-1000

Final output to be as given below.

	0 -500	500- 1000	1000-1500
Chennai	0	2	1
Bangalore	0	1	0

[MayilVahanan · Accepted solution]

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;

[MayilVahanan]

Hi

And use,

In Pivot table,

Location and Bucket as dimension

Count(Bucket) as Measure

Location	Bucket	500-1000	1000-1500
Bangalore		1	1
Chennai		2	0

[#4]

Hi,

Your answer is correct the only issue for me is even though i have given teh location in same table, in actual i am getting it from different table.

Now how do i create a key

[sasiparupudi1]

Please post your data or your qvw file