Sort file based on a key in another file
Tags:
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:
Last updated:
Discuss This Question: 10 Replies
I would use SQL to join and sequence the files
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?
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.
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
...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