topic Re: Aggr function in script in QlikView

Aggr function in script

Mon, 03 Aug 2015 11:12:50 GMT

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

Re: Aggr function in script

MayilVahanan — Mon, 03 Aug 2015 11:50:50 GMT

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 — Mon, 03 Aug 2015 11:51:43 GMT

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

Re: Aggr function in script

Mon, 03 Aug 2015 12:41:14 GMT

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

Re: Aggr function in script

sasiparupudi1 — Mon, 03 Aug 2015 12:52:13 GMT

Please post your data or your qvw file