Solved

Need help with a sorting problem using Delphi

Posted on 2003-11-05
13
214 Views
Last Modified: 2010-04-05
OK guy/gals, I have a tough problem that I keep running into walls on. This is primarily a data sorting (logic) problem that just happens to be written in Delphi, so I would like to have Delphi(6) code as the answer.

First off, the data looks like this: (tab delimited)

program     transaction type          datetime                           account num

acct.dls       RESET ACCT     RET  200311050944400          01 234031
acct.dls       RESET ACCT     RET  200311050944400          01 234031
acctbs.dls     ACCTINFO.EXE   RET  200311050944410          27 234031
acctbs.dls     ACCTINFO.EXE   RET  200311050944410          27 234031
acctnos.dls    ACCTINFO.EXE   RET  200311050944410          31 234031
acctnos.dls    ACCTINFO.EXE   RET  200311050944410          31 234031
ACCT.DLS       ACCT.EXE       RET  200311050944410          01 234031
ACCT.DLS       ACCT.EXE       RET  200311050944410          01 234031
               OPENCALL.EXE   BEG  200311050947260          00 234031
acctact.dls    OPENCALL.EXE   ADD  200311210948000          25 234031
acctvdte.dls   OPENCALL.EXE   RET  200311210948000          63 234031
acctvdte.dls   OPENCALL.EXE   RET  200311210948000          63 234031
acctbs.dls     OPENCALL.EXE   RET  200311210948050          27 234031
acctbs.dls     OPENCALL.EXE   RET  200311210948050          27 234031
               OPENCALL.EXE   END  200311210948060          99 234031
ACCT.DLS       ACCT.EXE       RET  200311210948060          01 234031
ACCT.DLS       ACCT.EXE       RET  200311210948060          01 234031
acct.dls       RESET ACCT     RET  200311210948260          01 234031
acct.dls       RESET ACCT     RET  200311210948260          01 234031
acctbs.dls     ACCTINFO.EXE   RET  200311210948260          27 234031
acctbs.dls     ACCTINFO.EXE   RET  200311210948260          27 234031
ACCT.DLS       ACCT.EXE       RET  200311210948260          01 234031
ACCT.DLS       ACCT.EXE       RET  200311210948260          01 234031
acct.dls       RESET ACCT     RET  200311050949110          01 106545
acct.dls       RESET ACCT     RET  200311050949110          01 106545
acctbs.dls     ACCTINFO.EXE   RET  200311050949110          27 106545
acctbs.dls     ACCTINFO.EXE   RET  200311050949110          27 106545
acctnos.dls    ACCTINFO.EXE   RET  200311050949110          31 106545
acctnos.dls    ACCTINFO.EXE   RET  200311050949110          31 106545
ACCT.DLS       ACCT.EXE       RET  200311050949110          01 106545
ACCT.DLS       ACCT.EXE       RET  200311050949110          01 106545
               OPENCALL.EXE   BEG  200311050949170          00 106545
acctbavl.dls   OPENCALL.EXE   ADD  200311050949210          22 106545
acctbs.dls     OPENCALL.EXE   RET  200311050949250          27 106545
acctbs.dls     OPENCALL.EXE   RET  200311050949250          27 106545
               OPENCALL.EXE   END  200311050949260          99 106545
ACCT.DLS       ACCT.EXE       RET  200311050949260          01 106545
ACCT.DLS       ACCT.EXE       RET  200311050949260          01 106545                    

data mapping:

datetime   start 36, length 15   (ie, 200311050949110)
account num  start 64 length 6  (ie, 234031)
transaction type:  start 16, length 14   ( ie, OPENCALL.EXE)


Problem:

These entries are suppose to be written to this file in datetime sequential order. For some reason, this doesn't always happen as above. Take account number 234031.  For transaction type OPENCALL.EXE it begins on 200311050947260 with this line:
   OPENCALL.EXE   BEG  200311050947260          00 234031
   (the BEG means BEGIN)

It ends with this line:  OPENCALL.EXE   END  200311210948060          99 234031

Notice that this begins on 11/5/2003 and ends on 11/21/2003.

Other types of transactions happen in between this time.


Outcome:

What I need to end up with is a method to process these transactions so that the account numbers are grouped together.  With the example above, if just sorted by datetime, the transactions for account num 234031 would be split like below:

acct.dls       RESET ACCT     RET  200311050944400          01 234031
acct.dls       RESET ACCT     RET  200311050944400          01 234031
acctbs.dls     ACCTINFO.EXE   RET  200311050944410          27 234031
acctbs.dls     ACCTINFO.EXE   RET  200311050944410          27 234031
acctnos.dls    ACCTINFO.EXE   RET  200311050944410          31 234031
acctnos.dls    ACCTINFO.EXE   RET  200311050944410          31 234031
ACCT.DLS       ACCT.EXE       RET  200311050944410          01 234031
ACCT.DLS       ACCT.EXE       RET  200311050944410          01 234031
               OPENCALL.EXE   BEG  200311050947260          00 234031
acct.dls       RESET ACCT     RET  200311050949110          01 106545
acct.dls       RESET ACCT     RET  200311050949110          01 106545
acctbs.dls     ACCTINFO.EXE   RET  200311050949110          27 106545
acctbs.dls     ACCTINFO.EXE   RET  200311050949110          27 106545
acctnos.dls    ACCTINFO.EXE   RET  200311050949110          31 106545
acctnos.dls    ACCTINFO.EXE   RET  200311050949110          31 106545
ACCT.DLS       ACCT.EXE       RET  200311050949110          01 106545
ACCT.DLS       ACCT.EXE       RET  200311050949110          01 106545
               OPENCALL.EXE   BEG  200311050949170          00 106545
acctbavl.dls   OPENCALL.EXE   ADD  200311050949210          22 106545
acctbs.dls     OPENCALL.EXE   RET  200311050949250          27 106545
acctbs.dls     OPENCALL.EXE   RET  200311050949250          27 106545
               OPENCALL.EXE   END  200311050949260          99 106545
ACCT.DLS       ACCT.EXE       RET  200311050949260          01 106545
ACCT.DLS       ACCT.EXE       RET  200311050949260          01 106545               TRANS.EXE      CHK  200311050950270          CS      
acctact.dls    OPENCALL.EXE   ADD  200311210948000          25 234031
acctvdte.dls   OPENCALL.EXE   RET  200311210948000          63 234031
acctvdte.dls   OPENCALL.EXE   RET  200311210948000          63 234031
acctbs.dls     OPENCALL.EXE   RET  200311210948050          27 234031
acctbs.dls     OPENCALL.EXE   RET  200311210948050          27 234031
               OPENCALL.EXE   END  200311210948060          99 234031
ACCT.DLS       ACCT.EXE       RET  200311210948060          01 234031
ACCT.DLS       ACCT.EXE       RET  200311210948060          01 234031
acct.dls       RESET ACCT     RET  200311210948260          01 234031
acct.dls       RESET ACCT     RET  200311210948260          01 234031
acctbs.dls     ACCTINFO.EXE   RET  200311210948260          27 234031
acctbs.dls     ACCTINFO.EXE   RET  200311210948260          27 234031
ACCT.DLS       ACCT.EXE       RET  200311210948260          01 234031
ACCT.DLS       ACCT.EXE       RET  200311210948260          01 234031


See how the OPENCALL.EXE BEG gets separated from the remaining OPENCALL transactions? (for account num 234031).  These need to stay together so I can have a BEG and END in sequence regardless of datetime. If no matching END record is found I need to remove the complete OPENCALL section (bad/incomplete data).

Sort by account number first then date second you say?  Keep in mind that I could have more transactions for the same account num throughout the file. There could be more transactions for acct num  234031 (not just OPENCALL ones) further down in the file for the 11/05/2003 date. If sorted by account num and then datetime, this would still separate my OPENCALL transactions.

If there are more OPENCALL transactions further down for the same account number, it wouldn't hurt to combine these, since it indicates someone went back in and made more changes. I'm just interested in net change. (Note: Normally there is many bytes of data after the account number. I just removed the actual data for this problem. It isn't pertinant to the sorting problem).


The original record itself is in a text file that gets read into a stringlist. Currently, I use another stringlist that contains the datetime, accountnumber, and incrementing integer that corresponds to the original stringlist index (a pointer, if you will, back to the location of the data).

You can use any method as long as the original data isn't changed. Doesn't matter how efficient it is. Need more clarification, please let me know.


NOTE:  I know this is a difficult, and probably time consuming problem but I need working code for the solution. Only working code get's the points. In the past I've been forced (highly suggested) that I split points between people that offered comments but no real solution simply because the "comments had value".  For this problem, only working code is of value to me.

Thanks


0
Comment
Question by:rutledgj
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9687626
listening . . . no time yet . . . but looks not so hard
0
 
LVL 12

Expert Comment

by:andrewjb
ID: 9687926
?? Sort by account type, then account number, then date time?

I'm not sure what you want exactly. Keeping account type together, but ordering by the first entry for each account type? or what? How about giving us the 'right' answer for your original list of transactions.

Also, we're not really here to write tested code for you. Pay someone to do that - this is presumably a work-related problem. It's the algorithm that you really want, isn't it?
0
 

Author Comment

by:rutledgj
ID: 9688009
Ideally, it would be sorted like this:


acct.dls       RESET ACCT     RET  200311050944400          01 234031
acct.dls       RESET ACCT     RET  200311050944400          01 234031
acctbs.dls     ACCTINFO.EXE   RET  200311050944410          27 234031
acctbs.dls     ACCTINFO.EXE   RET  200311050944410          27 234031
acctnos.dls    ACCTINFO.EXE   RET  200311050944410          31 234031
acctnos.dls    ACCTINFO.EXE   RET  200311050944410          31 234031
ACCT.DLS       ACCT.EXE       RET  200311050944410          01 234031
ACCT.DLS       ACCT.EXE       RET  200311050944410          01 234031
               OPENCALL.EXE   BEG  200311050947260          00 234031
acctact.dls    OPENCALL.EXE   ADD  200311210948000          25 234031
acctvdte.dls   OPENCALL.EXE   RET  200311210948000          63 234031
acctvdte.dls   OPENCALL.EXE   RET  200311210948000          63 234031
acctbs.dls     OPENCALL.EXE   RET  200311210948050          27 234031
acctbs.dls     OPENCALL.EXE   RET  200311210948050          27 234031
               OPENCALL.EXE   END  200311210948060          99 234031
acct.dls       RESET ACCT     RET  200311050949110          01 106545
acct.dls       RESET ACCT     RET  200311050949110          01 106545
acctbs.dls     ACCTINFO.EXE   RET  200311050949110          27 106545
acctbs.dls     ACCTINFO.EXE   RET  200311050949110          27 106545
acctnos.dls    ACCTINFO.EXE   RET  200311050949110          31 106545
acctnos.dls    ACCTINFO.EXE   RET  200311050949110          31 106545
ACCT.DLS       ACCT.EXE       RET  200311050949110          01 106545
ACCT.DLS       ACCT.EXE       RET  200311050949110          01 106545
               OPENCALL.EXE   BEG  200311050949170          00 106545
acctbavl.dls   OPENCALL.EXE   ADD  200311050949210          22 106545
acctbs.dls     OPENCALL.EXE   RET  200311050949250          27 106545
acctbs.dls     OPENCALL.EXE   RET  200311050949250          27 106545
               OPENCALL.EXE   END  200311050949260          99 106545
ACCT.DLS       ACCT.EXE       RET  200311050949260          01 106545
ACCT.DLS       ACCT.EXE       RET  200311050949260          01 106545
ACCT.DLS       ACCT.EXE       RET  200311210948060          01 234031
ACCT.DLS       ACCT.EXE       RET  200311210948060          01 234031
acct.dls       RESET ACCT     RET  200311210948260          01 234031
acct.dls       RESET ACCT     RET  200311210948260          01 234031
acctbs.dls     ACCTINFO.EXE   RET  200311210948260          27 234031
acctbs.dls     ACCTINFO.EXE   RET  200311210948260          27 234031
ACCT.DLS       ACCT.EXE       RET  200311210948260          01 234031
ACCT.DLS       ACCT.EXE       RET  200311210948260          01 234031


(I'm just concerned with getting the OPENCALL transactions sorted so they stay together for a given account number)


andrewjb: Yes, the algorithm that sorts the code is what I need. But hey, if you don't want to answer it, don't.
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9688130
rutledgj,
  Are all your transaction types in pairs (Start-end)? If not, are there any specific ones those are supposed to be in pairs?
...Snehanshu
0
 
LVL 12

Expert Comment

by:andrewjb
ID: 9688239
So it is:

Sort by (Account Number) then (AccountType) then (DateTime)

Split this sorted list into sub-lists where (AccountNumber) and (AccountType) are the same.

Sort these sub-lists by the first DateTime of each sublist.

Hey presto.

Bob's your uncle.


If you can't code that, I'm sure someone's got the time to do it for you.

Andrew.


0
 
LVL 17

Expert Comment

by:geobul
ID: 9688293
Hi,

If you really want OPENCALL transactions one after another only, this could be done.

1. Get first/next line and check if it is 'OPENCALL.EXE   BEG'. If 'NO' get next line until the end of the file
2. If 'YES' then remember the account number and the current position as FirstLine
3. repeat
4. Read next line
5. If it is 'OPENCALL.EXE' for the same account
5.1. If it is NOT 'END' then go to 4.
5.2. Else go to 1.
6. Else
7. Remember this position as AtLine
8. repeat
9. read next line
10. until this line is 'OPENCALL.EXE' for the same account
11. Move this line as AtLine (add it at position AtLine and delete it from here). Next line becomes AtLine + 1.
12. Go to 5.1.

Regards, Geo
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:rutledgj
ID: 9688415
<"Sort by (Account Number) then (AccountType) then (DateTime)">

OK, enlighten me. If this stuff is all alpha character, how is this going to sort properly?


Geo: I don't completely follow this logic after 5.1. Perhaps some kind of quasi code would be clearer.

Thanks
0
 
LVL 12

Expert Comment

by:andrewjb
ID: 9688466
... convert it to a number :-)

You've a string list, yes? then you parse each entry, so I presume you end up with, say, a TList of objects like:

class TMyRecord
begin
  AccountType : stirng;
  AccountNum : integer;
  DateOfTransation : integer;
end;

 which you can then sort with a custom sort function?
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 9688822
rutledgj,
  Here is a code that would have sorted it as you want if ALL the entries were only in pairs.
  Essentially, I tracked the first occurence of a (transaction type + account num) key, saved its time in mytimelst and retrieved this start time from the list on the second occurence.
  The sorting was thus done on Start time, transaction type, account num (For both records).
  Now, I notice that you can have more than just a pair for OPENCALL.EXE.
  Its almost midnight in my country, so I shall not further modify my code.
  But I think its fairly straightforward from here on. You have to make a special case for OPENCALL.EXE records such that MyTime gets updated whenever you find OPENCALL.EXE with a BEG and retrieve MyTime from MyTimeLst for all other OPENCALL.EXE.
  I hope that makes some sense.
  Cheers!
...Snehanshu


procedure TForm1.Button1Click(Sender: TObject);
Var
  MyLst, MyTimeLst: TStringList;
  i, MyIndex: integer;
  MyRec, MyKey, MyTime: String;
  MyEnd: Boolean;

begin
  MyLst := TStringList.Create;
  MyTimeLst := TStringList.Create;

  for i := 0 to Memo1.Lines.Count -1 Do
  Begin
    MyRec := Memo1.Lines[i];
    MyKey := copy(MyRec, 64, 6) + copy(MyRec, 16, 14);//AC + TT

    MyIndex := 0;

    If MyTimeLst.Values[MyKey] <> '' then //Get Key occurence count
      MyIndex := strtoint(MyTimeLst.Values[MyKey]);

    Inc(MyIndex);
    MyTimeLst.Values[MyKey] := IntToStr(MyIndex);//Update Key occurence count

    If MyIndex Mod 2 = 0 then //Second occurence = end, so retrive start time
    begin
      MyIndex := (MyIndex Div 2);
      MyKey := MyKey+Format('%.6d',[MyIndex]);
      MyTime := MyTimeLst.Values[MyKey];
    end
    Else//First occurence, so save start time
    begin
      MyIndex := ((MyIndex + 1) Div 2);
      MyKey := MyKey+Format('%.6d',[MyIndex]);
      MyTime := copy(MyRec, 36, 15);
      MyTimeLst.Values[MyKey] := MyTime;
    end;

    MyKey := MyTime + MyKey;//Start Time + AC + TT

    MyRec := MyKey+ MyRec;

    MyLst.Add(MyRec);

  End;

  MyLst.Sort;
  For i := 0 to MyLst.Count -1 Do
  Begin
    MyLst[i] := Copy(MyLst[i], 42, Length(MyLst[i]));
  End;

  Memo2.Lines.Assign(MyLst);
  MyLst.Free;
  MyTimeLst.Free;

end;
0
 
LVL 17

Assisted Solution

by:geobul
geobul earned 100 total points
ID: 9689103
Hi,

Something like (not tested and mistakes are possible):

CurrentLine := 0;
while true do begin // loop the file
  Inc(CurrentLine);
  ReadLine(CurrentLine);
  if File.EOF then exit;
  if it is 'OPENCALL.EXE   BEG' then begin // a transaction found
    FirstLine := CurrentLine;
    Account := this account;
    AtLine := CurrentLine;
    while true do begin // search next OPENCALL.EXE lines for the same account until 'OPENCALL END' or end of file
      Inc(CurrentLine);
      ReadLine(CurrentLine);
      if File.EOF then begin
        if AtLine > 0 then begin
          Delete all lines between FirstLine and AtLine; // no END found for the current transaction
        end;
        exit;
      end;

      if CurrentLine is 'OPENCALL.EXE' for the same account then begin // consecuitive line
        if CurrentLine is NOT 'END' then begin
          // do nothing
        end else begin // 'OPENCALL.EXE END' found
          AtLine := 0;
          break; // goes to the beginning of the outer loop
        end;
      end else begin // another type of transaction or account
        AtLine := CurrentLine;
        while true do begin // loop for the next OPENCALL line nonconsecuitive
          Inc(CurrentLine);
          ReadLine(CurrentLine);
          if File.EOF then begin
            if AtLine > 0 then begin
              Delete all lines between FirstLine and AtLine; // no END found for the current transaction
            end;
            exit;
          end;
          if CurrentLine is 'OPENCALL.EXE' for the same account then begin
            Add CurrentLine at position AtLine;
            Delete CurrentLine;
            CurrentLine := AtLine - 1;
            AtLine := 0;
            break;
          end;
        end;
      end;
    end;
  end;
end;

Regards, Geo
0
 

Author Comment

by:rutledgj
ID: 9689589
Let me ponder all of this for a while. Thanks for you input. Lot's of ideas.
0
 
LVL 5

Accepted Solution

by:
snehanshu earned 300 total points
ID: 9691523
EUREKA!
rutledgj, here's a fully working code for you:

procedure TForm1.Button1Click(Sender: TObject);
Var
  MyLst, MyTimeLst: TStringList;
  i, MyIndex, MyOpenIndex: integer;
  MyRec, MyKey, MyTime, MyOpenTime: String;
  MyEnd: Boolean;

begin
  MyLst := TStringList.Create;
  MyTimeLst := TStringList.Create;

  MyOpenIndex := 0;
  for i := 0 to Memo1.Lines.Count -1 Do
  Begin
    MyRec := Memo1.Lines[i];
    MyKey := copy(MyRec, 64, 6) + copy(MyRec, 16, 14);

    If Trim(UpperCase(copy(MyRec, 16, 14))) = 'OPENCALL.EXE' Then
    begin
      If Trim(UpperCase(copy(MyRec, 31, 3))) = 'BEG' Then
        MyOpenTime := copy(MyRec, 36, 15);
      MyTime := MyOpenTime;
      MyKey := MyKey + Format('%.6d',[i]);
    End
    else
    begin
      MyIndex := 0;
      If MyTimeLst.Values[MyKey] <> '' then
        MyIndex := strtoint(MyTimeLst.Values[MyKey]);

      Inc(MyIndex);

      MyTimeLst.Values[MyKey] := IntToStr(MyIndex);

      If MyIndex Mod 2 = 0 then
      begin
        MyIndex := (MyIndex Div 2);
        MyKey := MyKey+Format('%.6d',[MyIndex]);
        MyTime := MyTimeLst.Values[MyKey];
      end
      Else
      begin
        MyIndex := ((MyIndex + 1) Div 2);
        MyKey := MyKey+Format('%.6d',[MyIndex]);
        MyTime := copy(MyRec, 36, 15);
        MyTimeLst.Values[MyKey] := MyTime;
      end;
    End;

    MyKey := MyTime + MyKey;

    MyRec := MyKey+ MyRec;

    MyLst.Add(MyRec);

  End;

  MyLst.Sort;
  For i := 0 to MyLst.Count -1 Do
  Begin
    MyLst[i] := Copy(MyLst[i], 42, Length(MyLst[i]));
  End;

  Memo2.Lines.Assign(MyLst);
  MyLst.Free;
  MyTimeLst.Free;

end;
0
 

Author Comment

by:rutledgj
ID: 9700483
I'm splitting these because I think I'll use a combination of both code ideas. But snehanshu
 had the closest thing that works for me.

Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Windows Drag & Drop Location 2 85
proper way to parse url in delphi 2 135
Delphi - replicating a form 8 57
Delphi Form ownership 4 54
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now