1 2 Previous Next 18 Replies Latest reply on Aug 31, 2013 4:37 PM by keywords

    Counting the Number of Unique Values in a Field - JK

    jayankurian

      Hello,

      I followed the example calculation of the FM Knowledge Base "Counting the Number of Unique Values in a Field" , and I am not getting the right result.

       

      The instructional SQL statement example says ExecuteSQL ("SELECT COUNT (DISTINCT LocationField) FROM ContactTable" ; "" ; "").

       

      Based on the above example this is my calculation. ExecuteSQL ( "SELECT COUNT(DISTINCT MR #)FROM Radiology Performed Exam for TAT";"";"")

       

      But I am not getting any result, except the ? mark. I formated the field for zero decimal place, and my field is long enough to display 10 digits.

       

      Do I need to put anything within the two double quotes at the end of my SQL statment?

       

      Appreciate if anyone can be helpful for me.........

       

       

      Thank you!!!!!!!

        • 1. Re: Counting the Number of Unique Values in a Field - JK
          Hudi

          2 things for you to try.

           

          1. The "#" character might be throwing off your sql. change that and see what happens.

           

          2. The ExecuteSQL syntax is off in your calculation. Try this:

           

           

          ValueCount(

          ExecuteSQL ("SELECT DISTINCT (\"LocationField\") FROM \"ContactTable\"  " ; "" ; "")

           

          )

           

          I'm not sure about the COUNT DISTINCT Function in FM but maybe someone can weigh in on that.

           

          HTH

          • 2. Re: Counting the Number of Unique Values in a Field - JK
            keywords

            I haven't tried the ExecuteSQL path, but faced with the same need I got around this need with the following technique:

             

            1.     Create a value list showing all the values in the field via a cartesian join.

            2.     Create a calculation field using the formula:  ValueCount ( ValueListItems ( "fileName" ; "valueListName" ) )

            • 3. Re: Counting the Number of Unique Values in a Field - JK
              jayankurian

              Thank your for your help...

              Calculation resulted "1" for every record....

              • 4. Re: Counting the Number of Unique Values in a Field - JK
                jayankurian

                Thank you...

                I already have more than 1.2  million records in the DB and the DB grows with 25 thousand records by month...

                So my value list will be growing along with recod addition....Is it advisable to use such a huge value list?

                 

                Thank you....

                • 5. Re: Counting the Number of Unique Values in a Field - JK
                  erolst

                  It might be helpful to analyze your data model; 1,2 million records plus 25,000 per month sounds like a transaction table, and the field you are counting could be something that belongs into its own table and would then be referenced by its foreign key; this new table then is be the proper place to generate a value list.

                   

                  btw, what are you using the value list for?

                  • 6. Re: Counting the Number of Unique Values in a Field - JK
                    wimdecorte

                    Depends on the number of unique values.  Both the value list approach and the ExecuteSQL will work with the table's index.

                     

                    If speed does become an issue: do you need to keep all the records in one table or do the business rules allow for archiving anything over x days/weeks/months?

                    • 7. Re: Counting the Number of Unique Values in a Field - JK
                      jayankurian

                      Thank you Erolst,

                       

                      All records should be readly available inorder for me to build statistical reports(I have 6 years patients radiology study data in my system). One table holds all my data (50 fields).

                       

                      What I am trying accomplish is: Get a count of how many patients done study in a year to compare with the previous years patient count.

                      Each patient can have 1 exam or many exams(each exam has an associated unique # what we call is Accession#). Also each patient have a unique medical record number.

                       

                      Example:

                      Record 1

                      Name: Test, Patient 1

                      MR#: 786345

                      Accession#3518767

                      Exam Type: CAT Scan

                       

                      Record 2

                      Name: Test, Patient 1

                      MR#: 786345

                      Accession#3518892

                      Exam Type: X-Ray

                       

                      Record 3

                      Name: Test, Patient 1

                      MR#: 786345

                      Accession#3518973

                      Exam Type: MRI

                       

                      And so on..........

                       

                       

                      So, what I am looking for is, when I run a Patient count report the above example patient should count as 1(MR#786345) and output the total number of patients we treated within a certain period..Example: 1 year

                       

                      Hope it is clear for you.....

                       

                      Thank you so much for looking into it.....

                      • 8. Re: Counting the Number of Unique Values in a Field - JK
                        jayankurian

                        sorry I addressed the reply to erolst....

                         

                        All records should be readly available inorder for me to build statistical reports(I have 6 years patients radiology study data in my system). One table holds all my data (50 fields).

                         

                        What I am trying accomplish is: Get a count of how many patients done study in a year to compare with the previous years patient count.

                        Each patient can have 1 exam or many exams(each exam has an associated unique # what we call is Accession#). Also each patient have a unique medical record number.

                         

                        Example:

                        Record 1

                        Name: Test, Patient 1

                        MR#: 786345

                        Accession#3518767

                        Exam Type: CAT Scan

                         

                        Record 2

                        Name: Test, Patient 1

                        MR#: 786345

                        Accession#3518892

                        Exam Type: X-Ray

                         

                        Record 3

                        Name: Test, Patient 1

                        MR#: 786345

                        Accession#3518973

                        Exam Type: MRI

                         

                        And so on..........

                         

                         

                        So, what I am looking for is, when I run a Patient count report the above example patient should count as 1(MR#786345) and output the total number of patients we treated within a certain period..Example: 1 year

                         

                        Hope it is clear for you.....

                         

                        Thank you so much for looking into it.....

                        • 9. Re: Counting the Number of Unique Values in a Field - JK
                          erolst

                          jayankurian wrote:

                           

                          sorry I addressed the reply to erolst....

                           

                          Well, I don't mind …

                           

                          If your table is basically a join table between exam types and patients, then here's a suggestion to create a more sophisticated statistics tool: use an inventory table - so to speak.

                           

                          Create a new table that holds just three fields: ExamID, year and count. Now whenever you add a new exam entry for a ptient, check if there exists an entry in the new table where ExamID and year are equal to your new entry. If so, then update the count value; if not, create the new inventory entry (and update it, of course). The records in the Inventory table are now the foundation for your summaries.

                           

                          This assumes that a combination of patient and year within a given year is unique; if not, then here you could use ExecuteSQL on an inventory record to find all the distinct values of patientID for the respective combination of year and examID.

                           

                          Either way, now you can create a relationship from the Exams table to the Inventory table and display a portal to see a live and up-to-date summary by year. Or, create a layout in the inventory table to show a report summarized by year.

                          • 10. Re: Counting the Number of Unique Values in a Field - JK
                            davidhamannmedia

                            I would also say that you should think about a new data model. But back to the SQL statement. If I understand you right, then you need this:

                             

                            // total distinct (the sum)

                             

                            ExecuteSQL (

                                      "SELECT " &

                              "COUNT (DISTINCT \"MR#\") " &

                                      "FROM " &

                              "\"yourTable\" "

                                      ;"";""

                            )

                             

                            Example output: 4

                             

                            // distinct list of MR#

                             

                            ExecuteSQL (

                                      "SELECT " &

                              "DISTINCT \"MR#\" " &

                                      "FROM " &

                              "\"yourTable\" "

                                      ;"";""

                            )

                             

                            Example output:

                             

                            786345

                            786346

                            786347

                            786348

                             

                            If you want record counts just for a certain time period throw a "WHERE" in.

                            1 of 1 people found this helpful
                            • 11. Re: Counting the Number of Unique Values in a Field - JK
                              jayankurian

                              Hi David,

                              I modiied sql statment as you suggested, but I am not getting any result...

                              I would liove to attach a scaled version (one table/one layout/ couple of hundred records) of my file for you to glance thru...But not sure how to attach a file with it..

                               

                              Thank you

                              • 12. Re: Counting the Number of Unique Values in a Field - JK
                                davidhamannmedia

                                You can send me an email (hover over my profile to see it), if you want. I have a pretty long flight to DevCon tomorrow and could look into the file.

                                 

                                Best,

                                David

                                • 13. Re: Counting the Number of Unique Values in a Field - JK
                                  user17418

                                  It seems you are pointing to the wrong field:

                                   

                                  The instructional SQL statement example says ExecuteSQL ("SELECT COUNT (DISTINCT LocationField) FROM ContactTable" ; "" ; "").

                                   

                                  Based on the above example this is my calculation. ExecuteSQL ( "SELECT COUNT(DISTINCT MR #)FROM Radiology Performed Exam for TAT";"";"")

                                   

                                  But don't you want unique Patient names. So your SQL would use this field instead:

                                  ExecuteSQL ( "SELECT COUNT(DISTINCT PatientName) FROM Radiology Performed Exam for TAT";"";"")

                                   

                                  (of course you add some WHERE parameters to filter by date or MR#)

                                   

                                  Where (which table) are you calling this SQL command?

                                   

                                  • 14. Re: Counting the Number of Unique Values in a Field - JK
                                    jayankurian

                                    Hi Dave,

                                    Thanks again..

                                    I am attaching the modified file for you to review.. (ID and PW: support)

                                     

                                    Calculation field name: Unique MR_#s

                                     

                                    Report layout Name: MR# Count

                                     

                                    Script name : MR# Count

                                     

                                    I am sub summarizing the report by Modality to get a count of unique MR#s.

                                     

                                    Trailing grand summmary is total number of exams (count of Accession #) and Total count of patients (count of unique patient MR#s)

                                     

                                    Both, sub summary and trailing grand summary is outputing "?" for my patient count which is the unique MR# count.

                                     

                                    Sorry to say that I am not good with SQL statements....

                                     

                                    What I want is when I run this script I would like to get the total exam count as well as total patient count.

                                     

                                    My record search for the reporting is the Accession date (example: 6/1/13...6/30/13).

                                     

                                    Thank you..............

                                    1 2 Previous Next