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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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