Sort file based on a key in another file

RamvishakRamesh

2505 pts.

Tags:

AS/400

Physical File

Here is a scenario. I have a balance file which has account number and balance in it. I want to create a report out of it. The report should looks like this, it shows the Rank,(Rank 1 means account with highest Balance) Account number, and Balance order by Rank. There is another Account master file which has account number and Group. I need to rank the file based on the group. ie, if two accounts belongs to same group, it will have the same Rank. My balance file and account master is sorted based on account number. How will I group my balance file accounts based on the group?

Rank Number	A/C Number	Balance -SGD
1	546	156945422
2	884	30130349
3	655	14464216
3	655	13001166
4	051	1438940
4	051	11563451
5	766	3675331
5	766	924695
…	……………	…………
…	……………	…………
14	788	303788
14	401	3622922
14	459	984555
…	……………	…………
…	……………	…………
20	391	3425899
Total		235052235

Asked: October 23, 2013 11:44 AM Last updated: October 25, 2013 9:23 AM

Answer Wiki

Discuss This Question: 10 Replies

CharlieBrowne Oct 23, 2013 12:07 AM GMT

I would use SQL to join and sequence the files

62,340 pointsBadges:

report
philpl1jb Oct 23, 2013 3:17 PM GMT

Lots of options exist. What are you going to use to create the report?

Can you use SQL? Can you create a logical join file?

Or do you want to access the files independently?

54,090 pointsBadges:

report
TomLiotta Oct 24, 2013 2:34 AM GMT

It seems mostly clear until RankNumber 14 that has three different A/CNumbers and also seems to have a greater sub-total than RankNumber 5. Can you clarify what we are seeing? -- Tom

125,585 pointsBadges:

report
RamvishakRamesh Oct 24, 2013 5:55 AM GMT

Accounts under Rank 14 belongs to the same group. In short we need to sort based on balance, but have to consider multiple accounts under the same group. It is possible to use Opnqryf or SQL to achieve this.

2,505 pointsBadges:

report
TomLiotta Oct 24, 2013 8:00 AM GMT

Ah, group makes sense now. How does that change the issue with a higher balance sub-total than group 5? Is it simply that one of the balances in group 5, 3675331, is higher than the highest balance in group 14, 3622922? -- Tom

125,585 pointsBadges:

report
RamvishakRamesh Oct 24, 2013 10:55 AM GMT

Please ignore the balances values as I have edited it. It should appear in report in such a way that the highest balance will be the rank 1 record and least balance will be the last record. If there are multiple accounts under same group their total needs to be considered while giving the rank. eg- if acc 100,200 and 300 comes under group 111, then the total balance of 100,200 and 300 needs to be considered and if that total falls on to 3rd position, then all three accounts will get rank as 3. I guess we can achieve this with order by and group by in SQL. please advise

2,505 pointsBadges:

report
TomLiotta Oct 25, 2013 5:10 AM GMT

If there are multiple accounts under same group their total needs to be considered while giving the rank....
That seems to answer a major element. One more question should do it:
Ranks 3, 4 and 5 all show multiple entries from single A/C Numbers. Does that mean that the final report should always have lines for every row in the "balance file"? That is, if the "balance file" has 10,000 rows, then your final report should have 10,000 detail lines?
Tom

125,585 pointsBadges:

report
RamvishakRamesh Oct 25, 2013 5:21 AM GMT

Though the account number of Rank 3,4 and 5 are the same, the GL code were different so that was also considered. same account number with different GL code will appear as different lines and different account number within same group also will appear as different line.

2,505 pointsBadges:

report
RamvishakRamesh Oct 25, 2013 6:19 AM GMT

One approach. Please advise if anyone has a better idea. Have one work file with account number, Group and Balance(Order by group). Another work file with Group and Group's balance(order by blalance). Read each account from second work file and find the balance of all the accounts which is under that group from the first file. Write to the outfile and give rank as '1'. Repeat this process by incrementing the Rank.

2,505 pointsBadges:

report
TomLiotta Oct 25, 2013 9:23 AM GMT

...the GL code were different...
I've created some example tables for AccountMaster and AccountBalance, and I've used your example data to populate those files. Your example report has a 'Rank' number that requires determining sub-totals for a "group" before detail lines can be generated. I have built various SQL statements to test query elements.
Each time you change specifications like this, it's necessary for me to go back to the start and work everything through again. This change means my test AccountBalance file needs to be redefined and recreated. Then I need to work up a new way to populate it with data. Then I need to rebuild a couple queries to see if the data can be presented in a manner similar to your example.
That will all mean that another day will go by as I look for spare time again. This should have had an answer after the first day.
Are there other details that need to be known in order to get a proper Rank value and sorting/grouping as you need.
Please stay aware that specifications need to be clear at the beginning. Ideally, table formats, sample data and expected output are all needed. Table formats only need to include columns that affect the output. If we have sufficient details, we can give appropriate answers much sooner.
Tom

125,585 pointsBadges:

report