Grouping

[upaliwije]

Hi Friends

I have data table as follows. I have arranged them in a straight table and want to display records Name of Payee and amount  where the total amount per payee exceeds Rs.100000. Pls help me to write the expression for it

NAME OF PAYEE	AMOUNT
CHAMINDA SURANJITH	644
CITIZEN  DEVELOPMENT BANK	20,274
DAYATH	201
DAYATH	6,045
E P R PREMALAL EDIRISINGHE	149,357
FOWRUDEEN	2,000
G SENTHEEPAN	4,671
GENERAL PENDING	11,872
GENERAL PENDING	4,445
GENERAL PENDING	13,754
GENERAL PENDING	10,586
GENERAL PENDING	40,336
GENERAL PENDING	4,150
GENERAL PENDING	27,936
GENERAL PENDING	17,672
GENERAL PENDING	18,092
GENERAL PENDING	11,227
GENERAL PENDING	6,499
GENERAL PENDING	20,000
GENERAL PENDING	23,000
GENERAL PENDING	150,690
GENERAL PENDING	5,340
GENERAL PENDING	30,946
GENERAL PENDING	73,782
GENERAL PENDING	1,984
GENERAL PENDING	1,897
GENERAL PENDING	3,586
GENERAL PENDING	5,444
GENERAL PENDING	2,050
GENERAL PENDING	1,631
GENERAL PENDING	7,377
HAARIS	800
HAARIS	2,886
HAARIS	2,494
HAARIS	32,649
HAARIS	7,464
HAARIS	8,775
HAARIS	11,696
IRFAN	1,270
IRFAN	9,825
IRFAN	500
IYOOB	2,067
IYOOB	72,339
PERERA	126,457
PERERA	986
PERERA	160,338
PERERA	1,349
PERERA	48,624
M A NAFEES	7,982
M A NAFEES	807,563
M A NAFEES	94,882
MANSOOR	750
MANSOOR	267,959
MANSOOR	70,487
MANSOOR	24,782
MARSOOM	34,778
MARSOOM	1,333
MR P SRIMUGUNTHAN	5,107
MR. A.S. HABEEB MALIK	5,105
MR. G. RAMANAN	3,977
MR. G. RAMANAN	10,004
MR. M.A.M. RISWAN	1,370
MR. P. SURESKUMAR	4,732
MR. V. JAYACHANDRAN	111
MR. V. JAYACHANDRAN	2,234
MUFAREES	1,000
MUFAREES	18,401
MUFAREES	9,245
MUFAREES	54,057
MUFAREES	15,556
MUFAREES	283,535
MUFAREES	87,008
MUFAREES	152,640
N.G.G.GUNARATHNE	500
N.G.G.GUNARATHNE	42,172
NAVANEETHAN	19,816
PIRATHEEPAN	4,026
SAJEEWARABAWAN	52,835
SELLAPAR	574
THUSYNATHAN	1,176

[mightyqlikers]

Hi

Please use the below expression

=if(Sum(AMOUNT)>=100000,Sum(AMOUNT))

Regards

Samwise

[senpradip007 · Accepted solution]

Have a look at this....

[#4]

ya u can use aggr to analyze with respect to payee as follows:

=aggr(if(Sum(AMOUNT)>=100000,Sum(AMOUNT)) ,NAME OF PAYEE)

[upaliwije]

Thanks a lot

Pls also let me know way of grouping similar names with slight differences to single name as per example

m.Mansoor    1000

M.MANSOOR 2000

Mr.M. Mansoor 3000

P.A. Perera      2000

P.A.Pereara     1000

[#6]

Hi

  Write this expression in Edit script

table1:

load

NAME OF PAYEE,

sum(AMOUNT) as Total amount

resident table name group by NAME OF PAYEE;

load

*

resident  table1 where Total amount )>=100000;

it will give the who ever having amount grater  than 100000 only it will load

thanks

Manju