Sort file based on a key in another file

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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CharlieBrowne

    I would use SQL to join and sequence the files

    41,380 pointsBadges:
    report
  • philpl1jb

    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?

    49,850 pointsBadges:
    report
  • TomLiotta
    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

    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
    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
    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

    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
    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
    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

    ...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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following