Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear expert,
I need to show the last lab result from all the departments for patients in department 1, but I only want the patients from dept 1 appear in the list. The problem is that when no filter in the last test results column, the dept 2 patients show up in the straight table. Not sure how to solve this problem. For example, patient 1's last lest (7.7) is done in dept 2, it is what I need, but I do not want patients 8,9 10 in the list since they are from department 2
Please help. Sample project attached
Thanks in advance
Longmatch
EncounterID | PatientID | DeptID | FCMYN | A1C | VisitDate |
1 | 1 | 1 | 1 | 12.6 | 1/1/2017 |
2 | 1 | 1 | 1 | 13 | 1/2/2017 |
3 | 1 | 1 | 1 | 11.9 | 1/3/2017 |
4 | 1 | 2 | 1 | 7.7 | 1/4/2017 |
5 | 2 | 1 | 1 | 12.8 | 1/5/2017 |
6 | 2 | 1 | 1 | 13 | 1/6/2017 |
7 | 2 | 1 | 1 | 9.1 | 1/7/2017 |
8 | 3 | 1 | 1 | 10.3 | 1/8/2017 |
9 | 3 | 1 | 1 | 6.2 | 1/9/2017 |
10 | 3 | 2 | 1 | 11.6 | 1/10/2017 |
11 | 4 | 1 | 1 | 14 | 1/11/2017 |
12 | 4 | 1 | 1 | 9.4 | 1/12/2017 |
13 | 4 | 1 | 1 | 13.7 | 1/13/2017 |
14 | 5 | 1 | 1 | 7.7 | 1/14/2017 |
15 | 5 | 1 | 1 | 14.9 | 1/15/2017 |
16 | 6 | 1 | 1 | 7 | 1/16/2017 |
17 | 6 | 1 | 1 | 7.1 | 1/17/2017 |
18 | 7 | 1 | 1 | 5.9 | 1/18/2017 |
19 | 7 | 1 | 1 | 10.9 | 1/19/2017 |
20 | 7 | 1 | 1 | 5.4 | 1/20/2017 |
21 | 8 | 2 | 2 | 8.9 | 1/21/2017 |
22 | 8 | 2 | 2 | 12.5 | 1/22/2017 |
23 | 8 | 2 | 2 | 8.5 | 1/23/2017 |
24 | 9 | 1 | 2 | 11.6 | 1/24/2017 |
25 | 9 | 1 | 2 | 6.9 | 1/25/2017 |
26 | 9 | 1 | 2 | 5.6 | 1/26/2017 |
27 | 10 | 2 | 2 | 8.1 | 1/27/2017 |
28 | 10 | 2 | 2 | 10.1 | 1/28/2017 |
PatientID | FCMYN | Visits | LastVisit | LastA1C |
20 | 1/20/2017 | 10.1 | ||
1 | 1 | 4 | 1/4/2017 | 7.7 |
2 | 1 | 3 | 1/7/2017 | 9.1 |
3 | 1 | 3 | 1/10/2017 | 11.6 |
4 | 1 | 3 | 1/13/2017 | 13.7 |
5 | 1 | 2 | 1/15/2017 | 14.9 |
6 | 1 | 2 | 1/17/2017 | 7.1 |
7 | 1 | 3 | 1/20/2017 | 5.4 |
8 | 2 | 0 | - | 8.5 |
9 | 2 | 0 | - | 5.6 |
10 | 2 | 0 | - | 10.1 |
Hi,
If I understand you correctly, then we need to cut off patients with observations only in department 2. Then the sum of possible unique values of FCMYN should not be 2 (1 - only department1, 2 - only department 2. 3 - patient was in two departments).
An example in the attached file.
Regards,
Andrey
Check attached file please.
Hi Fei,
Thank you very much for your helps.
I am sorry I did not state my questions clearly and the sample is not appropriate. I just modified the sample. My questions is I want to show ALL A1C values for all patients whose FCMYN=1. For example, patient 1's last test result on 1/4/2017 is 7.7 done in dept 2. I cannot use filter FCMYN =1 since it will filter out the test in FCMYN=2. I also do not want patient 8,9 10 in the list since they are from department 2.
Hi,
If I understand you correctly, then we need to cut off patients with observations only in department 2. Then the sum of possible unique values of FCMYN should not be 2 (1 - only department1, 2 - only department 2. 3 - patient was in two departments).
An example in the attached file.
Regards,
Andrey
What I want to get is the latest test results for patients whose FCMYN=1, no matter where the test is done. However your answer is correct. I do not understand this syntax-Sum (DISTINCT TOTAL <PatientID> FCMYN) <>2. Can you explain? Thanks
Hi,
I think the problem is that when a patient is showing in both Dep1 and 2, how do you know if he BELONGS TO Dep1 or 2? It could be possible that a patient from Dep2 also has some result in Dep1, right? Then how can we know that this patient is belongs to Dep2 and not 1?
I know this is just some sample data. In your real full data, you may have Dep3 or more. We can hard code to make it work for now for just two departments but for your real full data, it is better to clearly define the rules. Maybe somehow load another field to define the patient department (not the department that the tests are done but the department that the patients belong to)?
I think your first sample app makes sense to me. There are both TestDepID and the FCMYN in that sample. And I think my first reply is the right answer (so for patient1 the last visit is 1/04/2017 and the result is 7.7). What is the problem for that?
" I cannot use filter FCMYN =1 since it will filter out the test in FCMYN=2". This is not right, using FCMYN=1 will NOT filter out the test in FCMYN2 when you have TestDepID because the "test in FCMYN=2" is in TestDepID=2, not FCMYN=2.
Hope this can help.
Cheers.
Fei