Enquiry:How to SORT Desc by SUM Field (Grouped)

Hi All,

I need help on creating enquiry for this requirement :
"Account Information Sorting By Sum of ONLINE.ACTUAL.BAL which Groupped by CUSTOMER"

Here is the illustration :



Any one has the logic (way) or trick..

Please help...

Thanks..
Emin

I think this is the third thread you have created for the same ;-)

In one of the thread jpb has asked this question “Did all the accounts of the customer are in same currency?” If yes then good else you have to convert the amount into respective currency you need to display.

Your requirement can be achieved by using file CUSTOMER.ACCOUNT in your enquiry and remaining options to design the output are available in ENQUIRY application itself.

QRY = SELECT FXXX.CUSTOMER.ACCOUNT
CALL EB.READLIST(QRY,LIST.CUS,"",R.TOT.CUS,ERR)

FOR I = 1 TO R.TOT.CUS
CALL F.READ(FN.CUS.AC,LIST.CUS<I>,R.CUS.REC,F.CUS.AC,ERR2)
ACC.IDS=DCOUNT(R.CUS.REC,FM)
FOR J=1 TO ACC.IDS
Y.ACC.NO=R.CUS.REC<J>
Y.ONLINE.ACT.BAL = TRANS("F.ACCOUNT",Y.ACC.NO,AC.ONLINE.ACTUAL.BAL,"")
Y.TOT.ONLINE.ACT.BAL = Y.TOT.ONLINE.ACT.BAL + Y.ONLINE.ACT.BAL
NEXT J
MY.DATA<-1> = LIST.CUS<I>:'*':Y.TOT.ONLINE.ACT.BAL
NEXT I

Dear danish.mir.ali,

Thank you for the help.

But my question is :
1. How to Sort by Y.TOT.ONLINE.ACT.BAL Descending.
2. How to get TOP 5 from Point 1

Thank You..

Muhaemin ,

You have to answer to "currency" question first (see durai611's post) !

I mean you can't mix apples and oranges ....

SUBROUTINE TEST.MAX

$INSERT I_COMMON
$INSERT I_EQUATE

MY.DATA<1,1> = 'A'
MY.DATA<1,2> = 100

MY.DATA<2,1> = 'B'
MY.DATA<2,2> = 200

MY.DATA<3,1> = 'C'
MY.DATA<3,2> = 300

MY.DATA<4,1> = 'D'
MY.DATA<4,2> = 400

MY.DATA<5,1> = 'E'
MY.DATA<5,2> = 500

Y.TOP.ARRAY = ''

CONVERT VM TO FM IN MY.DATA
FOR K = 1 TO 3
Y.MAX = MAXIMUM(MY.DATA)
LOCATE Y.MAX IN MY.DATA SETTING X.POS THEN
Y.TOP.ARRAY<K,1> = MY.DATA<X.POS-1>
Y.TOP.ARRAY<K,2> = MY.DATA<X.POS>
MY.DATA<X.POS> = 0
END
NEXT K
CRT Y.TOP.ARRAY

RETURN
END

Dear DUBLIN and durai611,

Yes, All the account has the same currency.

Thanks..

In this case you can design it in the Enquiry application itself. No need for routines.

Check the solution given by DUBLIN in the following link,
www.t24all.com/forum/30-t24-development-...ecord-displayed.html

Dear durai611,

Thank you for the solution. I still have a question, how to Sort The enquiry by total of ONLINE.ACTUAL.BAL which has been groupped by CUSTOMER.

Thanks.

Dear Muhaemin,

If you are planning to do it via a code then below is the ans to your query.

QRY = SELECT FXXX.CUSTOMER.ACCOUNT
CALL EB.READLIST(QRY,LIST.CUS,"",R.TOT.CUS,ERR)
FOR I = 1 TO R.TOT.CUS
CALL F.READ(FN.CUS.AC,LIST.CUS<I>,R.CUS.REC,F.CUS.AC,ERR2)
ACC.IDS=DCOUNT(R.CUS.REC,FM)
FOR J=1 TO ACC.IDS
Y.ACC.NO=R.CUS.REC<J>
Y.ONLINE.ACT.BAL = TRANS("F.ACCOUNT",Y.ACC.NO,AC.ONLINE.ACTUAL.BAL,"")
Y.TOT.ONLINE.ACT.BAL = Y.TOT.ONLINE.ACT.BAL + Y.ONLINE.ACT.BAL
NEXT J
MY.DATA<-1> = LIST.CUS<I>:'*':Y.TOT.ONLINE.ACT.BAL
NEXT I

This piece of code will create an array of Customer's with accumulated ONLINE.ACTUAL.BAL of all accounts which pertains to that customer. The result would be like

CUSTOMER TOT.ACTUAL.BAL
653421 2000
123455 3000
542134 4500
764532 1000
231455 25000

Now in the above list you need top 3 Customers which have the highest balance

CUSTOMER TOT.ACTUAL.BAL
231455 25000
542134 4500
123455 3000

Now this can be done by the logic i have provided later

Y.TOP.ARRAY = ''
CONVERT VM TO FM IN MY.DATA
FOR K = 1 TO 3
Y.MAX = MAXIMUM(MY.DATA)
LOCATE Y.MAX IN MY.DATA SETTING X.POS THEN
Y.TOP.ARRAY<K,1> = MY.DATA<X.POS-1>
Y.TOP.ARRAY<K,2> = MY.DATA<X.POS>
MY.DATA<X.POS> = 0
END
NEXT K
CRT Y.TOP.ARRAY
RETURN
END

Dear danish.mir.ali,

when I debug the Routine. the variable Y.MAX return NULL.

is it OK ?

You have to amend the syntax, i have given example of two different code.

CONVERT '*' TO FM IN MY.DATA

In debug first see whether MY.DATA is returning an array with customer and balances.

MAXIMUM is a function which will return maximum value in an array.

For e.g

MY.DATA = A^100^B^200^C^300
Y.MAX = MAXIMUM(MY.DATA)
Y.MAX = 300

Dear danish.mir.ali,

thank you a lot for your solusion..

It's Done.

Regards,
Emin