Page 1 of 2

SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 1:29 pm
by ramesh_khatri
Hi All,
Please can any one provide me sort code for below requirement...please let me knwo if more details required

Here we have three fields 1. Description(1:15), 2.CCY(Currency)(21:3), 3. AMT(Amount)(29:19)
I have to handle negative values also ...
Input data:


DESCRIPTION CCY AMT
------------- --- ----
INR 200
INR 200

---- ---
SUB TOT INR 400

INR 200
INR 200

---- ---
SUB TOT INR 400

EUR 1200
EUR 1200

---- ----
SUB TOT EUR 2400




KUW 200
KUW 200

---- ---
SUB TOT KUW 400

KUW 200
KUW 200

---- ---
SUB TOT KUW 400


zUR 1200
zUR 1200

---- ----
SUB TOT zUR 2400



Output data Required:


DESCRIPTION CCY AMT
---------- --- ----
INR 200
INR 200

---- ---
SUB TOT INR 400

INR 200
INR 200

---- ---
SUB TOT INR 400

CCY TOT INR 800
--- ---

EUR 1200
EUR 1200

---- ----
SUB TOT EUR 2400

CCY TOT EUR 2400
--- ---




KUW 200
KUW 200

---- ---
SUB TOT KUW 400

KUW 200
KUW 200

---- ---
SUB TOT KUW 400

CCY TOT KUW 800
--- ---


zUR 1200
zUR 1200

---- ----
SUB TOT zUR 2400

CCY TOT ZUR 2400
--- ---

GRND TOT 6400

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 1:59 pm
by Nik22Dec
Hi Ramesh,

I could not see any requirment. :?: :?: Can you check.

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 2:07 pm
by enrico-sorichetti
the input does not contain the total for each currency and the grand total
but I would never trust a financial organization which sums up in a grand total different currencies :geek:

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 2:26 pm
by ramesh_khatri
Hi Nik,

I have to insert Currency total and Grand total like

DESCRIPTION CCY AMT
---------- --- ----
INR 200
INR 200

---- ---
SUB TOT INR 400

INR 200
INR 200

---- ---
SUB TOT INR 400

CCY TOT INR 800
--- ---

EUR 1200
EUR 1200

---- ----
SUB TOT EUR 2400

CCY TOT EUR 2400
--- ---




KUW 200
KUW 200

---- ---
SUB TOT KUW 400

KUW 200
KUW 200

---- ---
SUB TOT KUW 400

CCY TOT KUW 800
--- ---


zUR 1200
zUR 1200

---- ----
SUB TOT zUR 2400

CCY TOT ZUR 2400
--- ---

GRND TOT 6400

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 2:40 pm
by BillyBoyo
ramesh_khatri wrote:
I have to insert Currency total and Grand total like



Currency total, OK. As enrico has already pointed out, prior to you posting this, the Grand total is nuts. You can't "add" currencies in their own value, only in a currency-equivalent (like a "base" currency) value.

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 2:47 pm
by enrico-sorichetti
most probably is just an exercise with poorly chosen descriptions

usually the stuff with subtotals and grand totals are done using
locations, departments, managers, employees

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 2:54 pm
by ramesh_khatri
Hi Enrico,


Its not an exercise it is a requirement whic i got in my Project.

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 2:56 pm
by enrico-sorichetti
:lol:

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 3:12 pm
by BillyBoyo
enrico, I'll see your :lol: and raise you :roll:

ramesh,

Go back to the person who gave you the requirment and ask them how they would like to describe the "Grand" total.

I'd suggest "Useless figure which might be very big, or very small, but likely somewhere in the middle, in actual value depending on the currencies in the sub-totals above - sorry, add them up yourself if you are interested in the answer, here's mine" 1920.

Either do it in a "base" currency, or ditch it. It is absurd.

Re: SORT for Aggrigate function

PostPosted: Mon Jan 02, 2012 3:53 pm
by Nik22Dec
Hi Ramesh,

I was toying with the idea but, could not think of a very neat idea. The only approach which comes to my mind is that you can split the input file into several files on the basis of currency, sum them separately, put the currency total as trailer in each file in the desired format & finally merge all the files.

I agree with Enrico & Billy that adding two different currencies does not make any sense,but,still if you want to do it, you can do that by just copying the Currency totals in a temp file & then performing addition on it separately. Clubbing can then be done at the end.

I know,it is a very clumsy approach & I really hope somebody should be able to propose a better approach.