SORT for Aggrigate function



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

SORT for Aggrigate function

Postby ramesh_khatri » Mon Jan 02, 2012 1:29 pm

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
ramesh_khatri
 
Posts: 3
Joined: Mon Jan 02, 2012 11:11 am
Has thanked: 0 time
Been thanked: 0 time

Re: SORT for Aggrigate function

Postby Nik22Dec » Mon Jan 02, 2012 1:59 pm

Hi Ramesh,

I could not see any requirment. :?: :?: Can you check.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: SORT for Aggrigate function

Postby enrico-sorichetti » Mon Jan 02, 2012 2:07 pm

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:
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: SORT for Aggrigate function

Postby ramesh_khatri » Mon Jan 02, 2012 2:26 pm

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
ramesh_khatri
 
Posts: 3
Joined: Mon Jan 02, 2012 11:11 am
Has thanked: 0 time
Been thanked: 0 time

Re: SORT for Aggrigate function

Postby BillyBoyo » Mon Jan 02, 2012 2:40 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SORT for Aggrigate function

Postby enrico-sorichetti » Mon Jan 02, 2012 2:47 pm

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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: SORT for Aggrigate function

Postby ramesh_khatri » Mon Jan 02, 2012 2:54 pm

Hi Enrico,


Its not an exercise it is a requirement whic i got in my Project.
ramesh_khatri
 
Posts: 3
Joined: Mon Jan 02, 2012 11:11 am
Has thanked: 0 time
Been thanked: 0 time

Re: SORT for Aggrigate function

Postby enrico-sorichetti » Mon Jan 02, 2012 2:56 pm

:lol:
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: SORT for Aggrigate function

Postby BillyBoyo » Mon Jan 02, 2012 3:12 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SORT for Aggrigate function

Postby Nik22Dec » Mon Jan 02, 2012 3:53 pm

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.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post