Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Need help with a sorting problem using Delphi

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
rutledgj
Asked:
rutledgj
  • 4
  • 3
  • 3
  • +2
2 Solutions
 
kretzschmarCommented:
listening . . . no time yet . . . but looks not so hard
0
 
andrewjbCommented:
?? 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
 
rutledgjAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
snehanshuCommented:
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
 
andrewjbCommented:
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
 
geobulCommented:
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
 
rutledgjAuthor Commented:
<"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
 
andrewjbCommented:
... 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
 
snehanshuCommented:
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
 
geobulCommented:
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
 
rutledgjAuthor Commented:
Let me ponder all of this for a while. Thanks for you input. Lot's of ideas.
0
 
snehanshuCommented:
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
 
rutledgjAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now