Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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
1 Solution

Accepted Solutions
MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

4 Replies
MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MayilVahanan

Hi

And use,

In Pivot table,

Location and Bucket as dimension

Count(Bucket) as Measure

Location Bucket 500-10001000-1500
Bangalore11
Chennai20
Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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
Master III
Master III

Please post your data or your qvw file