Alpha Video Training
Results 1 to 6 of 6

Thread: Distinct Count

  1. #1
    Member
    Real Name
    Eric Hanson
    Join Date
    Apr 2011
    Posts
    46

    Default Distinct Count

    I saw some posts on this topic, but none really answered the question. In crystal reports you can summarize a field by using a distinct count function that only counts the field values that are different. i.e. count of dates 1/1/11, 1/1/11, 1/1/11, 1/2/11 = distinct count of 2. I tried creating a iff statement that evaluated each field value to say iif(refnumber<>previous(refnumber),1,0), iif(refnumber=previous(refnumber),0,1) both attempts returned 1 for each record even if they were repeated values. The previous() function in CR has always worked for me, but it doesn't seem like it is even evaluating the previous field value in Alpha 5. Any ideas or does anyone know why the previous() function does not work? The prev() function solved many problems for me in CR and I would like to be able to use it in Alpha 5.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Distinct Count

    The previous function requires that the field name be quoted.

    iif(refnumber=previous("refnumber"),0,1)

    Another way to get that distinct count is

    Code:
    w_count(table.external_record_content_get("your_table_name","any_field_name","distinct_test_field_name",".t. .and. unique_key_value()"),crlf())
    Last edited by Stan Mathews; 06-13-2011 at 01:42 PM.

  3. #3
    Member
    Real Name
    Eric Hanson
    Join Date
    Apr 2011
    Posts
    46

    Default Re: Distinct Count

    Sorry I didn't add the quotes in my post, but I did put them in the formula and it didn't work. Also, I'm not sure what the code is supposed to mean. Is ("any_field_name","distinct_test_field_name",".t. .and. unique_key_value()"),crlf()) all supposed to be an expression?

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Distinct Count

    Copy my whole expression above. Paste it into the expression builder somewhere or in the interactive window preceed it with a question mark (stands for print to screen).

    Change the your_table_name to your actual table name.
    Change any_field_name to any field name in your table.
    Change distinct_test_field_name to the name of the field holding the values you want to "count the distinct values of"

    If in the interactive window, hit the enter key.

  5. #5
    Member
    Real Name
    Eric Hanson
    Join Date
    Apr 2011
    Posts
    46

    Default Re: Distinct Count

    okay, thank you. Also the iff statement I tried using before works now, so I must have done something wrong earlier. Thank you.

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Distinct Count

    Previous() depends on the current order of the table if you do not specify the optional second parameter. You may have had the table ordered on something other than the date field in question.

Similar Threads

  1. select distinct from dbf table
    By moiola in forum Application Server Version 9 - Web/Browser Applications
    Replies: 2
    Last Post: 08-11-2008, 09:40 AM
  2. Licensed user count and network users count
    By Rokrz in forum Alpha Four Versions 7 and 8
    Replies: 0
    Last Post: 05-17-2007, 11:49 AM
  3. Select Distinct Query
    By paul.guillou in forum Alpha Five Version 7
    Replies: 3
    Last Post: 01-23-2006, 12:44 PM
  4. Distinct Count
    By George Bustos in forum Alpha Five Version 4
    Replies: 2
    Last Post: 01-15-2004, 08:41 AM
  5. Distinct
    By Ken Davis in forum Alpha Five Version 5
    Replies: 5
    Last Post: 10-25-2002, 07:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •