asked on

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

kretzschmar

listening . . . no time yet . . . but looks not so hard

andrewjb

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

rutledgj

ASKER

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.

snehanshu

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

andrewjb

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.

geobul

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

rutledgj

ASKER

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

andrewjb

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

snehanshu

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;

SOLUTION

geobul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rutledgj

ASKER

Let me ponder all of this for a while. Thanks for you input. Lot's of ideas.

ASKER CERTIFIED SOLUTION

snehanshu

membership