How to count distinct records in a table?

Hi All,
I have a table with "Table Name,Employee No.,Line No." as keys. You can enter the same employee no. more than once in this table. Now i want to count the employee numbers in this table onces. for example, If i have 1222, 1222, 1222, 1333, 1333, 1444 employees numbers recorded in the table, i want to count 1222 as one record, 1333 as one and 1444 as one. So i should have a total of 3 records. Also, i want to do this counting withing a specified date range. Say i want all the records in the table withing say 010117...010317 date range which i get using Filter := "Employee".GETFILTERS; expression in the onPreReport. Any idea on how to do this please? Thanks.

Best Answer

Answers

  • ishyampandeyishyampandey Member Posts: 22
    Take one EmpVar
    Now OnAfterrecord write this condition
    IF EmpVar <> Table."Employee NO" THEN
    BEGIN
    EmpCount +=1;
    END;
    EmpVar := Table."Employee NO";

    Do not clear any var.
    And apply filter on predataitem
  • Nav_ilNav_il Member Posts: 30
    Thanks ishyampandey . However, you cannot do this "IF EmpVar <> Table."Employee NO"". It means you are comparing an integer (EmpVar) with a code (Employee number). Also why apply filter on Predataitem? I have already appied it on onPreReport.
  • RockWithNAVRockWithNAV Member Posts: 1,139
    You need to build a logic, there's no any keyword as distinct like SQL which exist here.
  • Nav_ilNav_il Member Posts: 30
    Hi RockwithNav, thanks for the comment. Yes, I am aware that there is no keyword like DISTINCT, and I have been thinking of logics which are not working. For example, I tried to compare previous Employee number with the current one, if they are the same, I skip counting and if they are not the same, I count. But I don't know how to get previous employee number which is a code and compare it with the current employee number. Do you know how to do that?

  • ishyampandeyishyampandey Member Posts: 22
    Nav_il wrote: »
    Thanks ishyampandey . However, you cannot do this "IF EmpVar <> Table."Employee NO"". It means you are comparing an integer (EmpVar) with a code (Employee number). Also why apply filter on Predataitem? I have already appied it on onPreReport.

    Is your Employee no is integer type?
  • Nav_ilNav_il Member Posts: 30
    No, My Employee No. is a Code datatype.
  • Nav_ilNav_il Member Posts: 30
    Thanks ishyampandey. This works.
  • guidorobbenguidorobben Member Posts: 157
    edited 2018-08-22
    Just build a Query. Fast and simple. Oeps.. Just say this is for the classic Client.
  • hydharthydhart Member Posts: 20
    you can use temp table to store each record, and insert field you wish to count distinct to primary key and count the temp table.
Sign In or Register to comment.