Recent

Author Topic: Count similar entries in Listbox  (Read 3304 times)

ironman139

  • New Member
  • *
  • Posts: 36
Count similar entries in Listbox
« on: September 18, 2017, 08:05:52 pm »
Hello together.

I´ve programmed a bigger case management software for our business. (SQL based).
Now i want to programm some statistics.
I´ll read all cases in a ListBox.
This one looks like:
Customer 1
Customer 2
Customer 3
Customer 1
Customer 3

for example.

So how can i count how oven each customer is in the Listbox and have it like:
Customer 1 -> 2 Cases
Customer 2 -> 1 Case
Customer 3 -> 2 Cases

Regards Tim

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Count similar entries in Listbox
« Reply #1 on: September 18, 2017, 08:17:07 pm »
Code: SQL  [Select][+][-]
  1. SELECT COUNT(CustomerName) FROM customer_data
  2. INNER  JOIN customer ON customer_data.Customer_ID = Customer.ID
  3. GROUP BY CustomerName.
  4.  
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

ironman139

  • New Member
  • *
  • Posts: 36
Re: Count similar entries in Listbox
« Reply #2 on: September 18, 2017, 09:53:38 pm »
Code: SQL  [Select][+][-]
  1. SELECT COUNT(CustomerName) FROM customer_data
  2. INNER  JOIN customer ON customer_data.Customer_ID = Customer.ID
  3. GROUP BY CustomerName.
  4.  

Thanks,

but is there any possibility to do it in the Listbox ?

I´ve found this somewhere else.

Code: Pascal  [Select][+][-]
  1. begin
  2.  
  3.   ListBox1.Sorted := true;
  4.  
  5.   count := 1;
  6.  
  7.   lastString := '';
  8.  
  9.   for i:=0 to ListBox1.Items.Count-1 do begin
  10.     thisString := ListBox1.Items[i];
  11.  
  12.     if thisString = lastString then
  13.       count := count + 1
  14.     else
  15.       if lastString <> '' then begin
  16.          ListBox2.Items.Add(lastString + ' ' + IntToStr(count));
  17.          count := 1;
  18.       end;
  19.  
  20.  
  21.  
  22.  
  23.  
  24.       // Added
  25.  
  26.       lastString := thisString;
  27.   end;
  28.  
  29.   if count > 1 then
  30.     ListBox2.Items.Add(lastString + ' ' + IntToStr(count));
  31.  
  32. end;

But i get everytime that the Identifier "count" not found
« Last Edit: September 18, 2017, 10:23:57 pm by ironman139 »

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Count similar entries in Listbox
« Reply #3 on: September 18, 2017, 10:33:37 pm »
Code: Pascal  [Select][+][-]
  1. procedure GetCount(const aDataset:TDataset; const aFieldName:string):Tstringlist;
  2. var
  3.   vFld:TField;
  4.   vIdx:Integer;
  5. begin
  6.   Result := TStringlist.Create;
  7.   try
  8.     Result.Sorted := True;
  9.     Result.AllowDuplicates := alIgnore;
  10.     vFld := aDataset.FieldByname(AFieldName);
  11.     aDataset.First;
  12.     while not aDataset.eof do begin
  13.       vIdx := result.Indexof(afld.AsString);
  14.       if vIdx > -1 then result.objects[vIdx] := TObject(Integer(Result.objects[vIdx])+1) else result.add(vFld.AsString,TObject(1));
  15.       aDataset.Next;
  16.     end;
  17.   except
  18.     on e:Exception do begin
  19.       Result.Free;
  20.       Raise;
  21.     end;
  22.   end;
  23. end;
  24.  
  25. Function ToListBox(const aDataset:TDataset; aFieldName:String; aListBox:TListbox)
  26. var
  27.   vData:TStringlist;
  28.   vCntr : integer;
  29. begin
  30.   vData := GetCount(aDAtaset, aFIeldname);
  31.   alistbox.items.beginupdate;
  32.   try
  33.     alistbox.items.clear;
  34.     for vCntr := 0 to vData.Count -1 do begin
  35.       aListbox.items.Add(Vdata.Strings[vCntr]+'('+inttostr(integer(vDAta.Objects[vCntr]))+')';
  36.     end;
  37.   finally
  38.     aListbox.Items.EndUdate;
  39.     vData.Free;
  40.   end;
  41. end;
  42.  
Disclaimer:
  The code above was typed in the browser and it should be considered a pseudo code on how something like that could work pontentially not to be confused as a solution. The reader is expected to read the implementation of TStringlist and create a new class based on it that will be able to save strings and integers instead of objects or create a class to add in the stringlist that holds the actuall count and other relevant information instead of hacking around the tobject definition.
« Last Edit: September 18, 2017, 10:47:58 pm by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

howardpc

  • Hero Member
  • *****
  • Posts: 4144
Re: Count similar entries in Listbox
« Reply #4 on: September 18, 2017, 11:29:21 pm »
You could try dropping a listbox, memo and button on the main form of a new project. Populate the listbox with desired items, and try this:
Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, Forms, StdCtrls;
  9.  
  10. type
  11.  
  12.   TForm1 = class(TForm)
  13.     AnalyseButton: TButton;
  14.     ListBox1: TListBox;
  15.     Memo1: TMemo;
  16.     procedure AnalyseButtonClick(Sender: TObject);
  17.   private
  18.     procedure SummariseUnique(aStrings: TStrings; newList: TStrings);
  19.   end;
  20.  
  21. var
  22.   Form1: TForm1;
  23.  
  24. implementation
  25.  
  26. {$R *.lfm}
  27.  
  28. procedure TForm1.AnalyseButtonClick(Sender: TObject);
  29. begin
  30.   SummariseUnique(ListBox1.Items, Memo1.Lines);
  31. end;
  32.  
  33. procedure TForm1.SummariseUnique(aStrings: TStrings; newList: TStrings);
  34. var
  35.   sl: TStringList;
  36.   s: String;
  37.   i: Integer;
  38.   j: PtrUInt;
  39.  
  40.   function GetEnglishPlural(num: PtrUInt): String;
  41.   begin
  42.     case num of
  43.       1: Exit('');
  44.       else Exit('s');
  45.     end;
  46.   end;
  47.  
  48. begin
  49.   if not Assigned(aStrings) or not Assigned(newList) then
  50.     Exit;
  51.   sl:=TStringList.Create;
  52.   try
  53.     sl.Duplicates:=dupError;
  54.     sl.Sorted:=True;
  55.     for s in aStrings do begin
  56.       i:=sl.IndexOf(s);
  57.       if (i > -1) then
  58.         sl.Objects[i]:=TObject(PtrUInt(Succ(PtrUInt(sl.Objects[i]))))
  59.       else
  60.         sl.AddObject(s, TObject(PtrUInt(1)));
  61.     end;
  62.     newList.Clear;
  63.     for i:=0 to sl.Count-1 do begin
  64.       j:=PtrUInt(sl.Objects[i]);
  65.       newList.Add('%s -> %d Case%s',[sl[i], j, GetEnglishPlural(j)]);
  66.     end;
  67.   finally
  68.     sl.Free;
  69.   end;
  70. end;
  71.  
  72. end.

ironman139

  • New Member
  • *
  • Posts: 36
Re: Count similar entries in Listbox
« Reply #5 on: September 19, 2017, 03:32:21 pm »
I have it in the Listbox.

Just one question left.
How can I sort by number in a Listbox.
I have now something like this:

2 Customer A
8 Customer C
5 Customer B

When I try the normale .sorted=true; I`ll get the sort by name not by number.
How can I sort by number ?

Regards Tim

Fungus

  • Sr. Member
  • ****
  • Posts: 353
Re: Count similar entries in Listbox
« Reply #6 on: September 19, 2017, 04:33:46 pm »
Have you considered using a TListView with ViewStyle=Report with a column for each "per customer field" and defining a OnCompare event? Or simply do the sorting and grouping on the database (using SQL) and set the ListView to OwnerData? TListBox is quite limited..

howardpc

  • Hero Member
  • *****
  • Posts: 4144
Re: Count similar entries in Listbox
« Reply #7 on: September 19, 2017, 05:45:59 pm »
To do this using only a listbox, you can use the code I gave above, but add a compare function, and adapt the SummariseUnique() procedure to read as follows:
Code: Pascal  [Select][+][-]
  1. function SortByNumericObject(List: TStringList; Index1, Index2: Integer): Integer;
  2. var
  3.   objInt1, objInt2: PtrUInt;
  4. begin
  5.   objInt1:=PtrUInt(List.Objects[Index1]);
  6.   objInt2:=PtrUInt(List.Objects[Index2]);
  7.   if (objInt1 > objInt2) then
  8.     Exit(1)
  9.   else if (objInt1 < objInt2) then
  10.     Exit(-1)
  11.     else Exit(0);
  12. end;
  13.  
  14. procedure TForm1.SummariseUnique(aStrings: TStrings; newList: TStrings);
  15. var
  16.   sl: TStringList;
  17.   s: String;
  18.   i: Integer;
  19.   j: PtrUInt;
  20.  
  21.   function GetEnglishPlural(num: PtrUInt): String;
  22.   begin
  23.     case num of
  24.       1: Exit('');
  25.       else Exit('s');
  26.     end;
  27.   end;
  28.  
  29. begin
  30.   if not Assigned(aStrings) or not Assigned(newList) then
  31.     Exit;
  32.   sl:=TStringList.Create;
  33.   try
  34.     sl.Duplicates:=dupError;
  35.     sl.Sorted:=True;
  36.     sl.SortStyle:=sslAuto;
  37.     for s in aStrings do begin
  38.       i:=sl.IndexOf(s);
  39.       if (i > -1) then
  40.         sl.Objects[i]:=TObject(PtrUInt(Succ(PtrUInt(sl.Objects[i]))))
  41.       else
  42.         sl.AddObject(s, TObject(PtrUInt(1)));
  43.     end;
  44.     sl.SortStyle:=sslUser;
  45.     sl.CustomSort(@SortByNumericObject);
  46.     newList.Clear;
  47.     for i:=0 to sl.Count-1 do begin
  48.       j:=PtrUInt(sl.Objects[i]);
  49.       newList.Add('%s -> %d Case%s',[sl[i], j, GetEnglishPlural(j)]);
  50.     end;
  51.   finally
  52.     sl.Free;
  53.   end;
  54. end;

Almir.Bispo

  • Jr. Member
  • **
  • Posts: 91
  • CSV Comp DB is the Best NoSQL
    • CSV Comp DB (NoSQL)
Re: Count similar entries in Listbox
« Reply #8 on: September 19, 2017, 07:58:58 pm »
I did it so:
Code: Pascal  [Select][+][-]
  1. {
  2. Function to count and order on listbox to Lazarus Comunity
  3. Almir Bispo
  4. date:19/09/2017
  5. }
  6. function count_items(list_string:string):string;
  7. var default,res,numb,list:Tstringlist;
  8.    i,L,count:integer;
  9. begin
  10. list:=Tstringlist.create;
  11.  default:=Tstringlist.create;
  12. res:=Tstringlist.create;
  13. numb:=Tstringlist.create;
  14. count:=0;
  15. list.text:=list_string;
  16. default.Text:=list.text;
  17. //find and count each item on list
  18. for i:= 0 to default.Count-1 do
  19. begin
  20.  for L:= 0 to default.Count-1 do
  21.  begin
  22.   if default.Strings[i]=list.strings[L] then
  23.   begin
  24.   //if exists,increment it
  25.     inc(count);
  26.     //prepare new list
  27.     if res.IndexOf(list.strings[L])=-1  then
  28.     begin
  29.     res.add(default.Strings[i]);
  30.     numb.add('0');
  31.     end;
  32.  
  33.    end;
  34.  
  35.  end;
  36.   //ocurrency list
  37.   numb.Strings[res.IndexOf(default.Strings[i])]:=inttostr(count);
  38.   count:=0;
  39. end;
  40. //resolve the exibition
  41. for i:=0 to res.count-1 do
  42. begin
  43.  res.Strings[i]:=res.strings[i]+'> Case(s) '+numb.Strings[i];
  44. end;
  45. //Order by Initial letter order
  46. res.Sorted:=true;
  47.  
  48. result:=(res.text);
  49.  
  50.  default.free;
  51.  res.free;
  52.  numb.free;
  53.  
  54. end;
  55. procedure TForm1.Button1Click(Sender: TObject);
  56. begin
  57. //how to use
  58. showmessage(  count_items( listbox1.Items.Text ) );
  59. end;
  60.                                                  
  61.  

My blog http://adltecnologia.blogspot.com.br
CSV Comp DB Developer {Pascal Lover}

howardpc

  • Hero Member
  • *****
  • Posts: 4144
Re: Count similar entries in Listbox
« Reply #9 on: September 19, 2017, 10:34:31 pm »
ironman139 is looking for both a count of customer frequency, and the report to be ordered numerically.
See below for the effect of the code I gave.

ironman139

  • New Member
  • *
  • Posts: 36
Re: Count similar entries in Listbox
« Reply #10 on: September 20, 2017, 03:36:14 pm »
Thank you guys.

Perfect  8-) ;)

 

TinyPortal © 2005-2018