| Author |
Message |
drummondj
Newbie
Joined: 13 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 5
|
   Topic: Using AND
Posted: 28 Feb 2014 at 7:07am |
|
Hi
I have a table of test results.
I need to select records where TestA=10 and TestB=20
I have set formula for each test, eg:
FormA
If TestA = 10
then 1;
FormB
If TestB = 20
then 1;
Each formula returns results individually.
But when I try and combine them I get no records returned.
What am I doing wrong.
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 03 Mar 2014 at 5:26am |
|
so if testa= 10 and testb=20 then 1
returns nothing?
simple question, are you sure that there are records that satisfy the condition?
do you have the formulas displaying next to the values so that you can trouble shoot? That would be the first thing that I would do, would be to verify if the report is showing 1 for both formulas on the same line to verify the data and the formulas. If there are no rows with 2 1's then I and you think there should be, I would verify that there is data that matches
|
|
IP Logged |
|
drummondj
Newbie
Joined: 13 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 04 Mar 2014 at 7:29am |
|
Thanks for the response it is appreciated.
Yes there are records that should meet the criteria - there are 3 that overlap.
In the formula TESTA I have:
If Test=TestA and Result = 10 then 1
In the formula TESTB I have:
If Test=TestB and Result = 20 then 1
And then I try and combine themin TESTAB
If TESTA= 1 and TESTb = 1 then 1
then I select if TESTAB=1
Thanks
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 04 Mar 2014 at 8:35am |
|
but for the row, can the test be both A & B...so it is probably always false...I would think.
Again, 1 field cannot hold 2 different values at the same item...or am I missing something?
|
|
IP Logged |
|
drummondj
Newbie
Joined: 13 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 04 Mar 2014 at 11:07am |
|
Hi
No they are not in the same row. There are two columns Test and Result.
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Mar 2014 at 4:25am |
I think you are looking for a group select criteria here.
You need 2 formula, one for A and one for B
you have to group on the "test number" (whatever identifies a set of rows as one test) and set seperate group level criteria using your results from the two formula together
SUM(FormA,testnumber)>0 and sum(FormB,testnumber)>0
|
|
IP Logged |
|
drummondj
Newbie
Joined: 13 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 06 Mar 2014 at 2:59am |
|
Thanks DB, but I'm sorry you've lost me there, maybe I should explain a bit more.
This is a table of results of pathology blood tests and results e.g. glucose and cholesterol.
In the table a patient has many rows, one for each test performed.
I need to select the patients where the test is glucose and it has a result of > 8, and where the same patient has a cholesterol test with a results of > 5. Ideally I don't need to list the results - just the patients details.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Mar 2014 at 3:53am |
easiest to do in SQL with a store proc but you can do it with a command too.
If you want a solution using your existing process
group on client
create two formulas to flag each of your conditions
//glucose
if test=glucose and result>8 then 1 else 0
//cholesterol
if test=cholesterol and result>5 then 1 else 0
sum each of these at the client group level
now any client with both sums >0 are your clients you want
use that in the group select
SUM(@glucose,clientid)>0 and sum(@cholesterol,clientid)>0
Likely you have date issues to include but this should give you an idea how this can work
|
|
IP Logged |
|
drummondj
Newbie
Joined: 13 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 5
|
Posted: 08 Mar 2014 at 6:30am |
|
It works DB - you are a star.
I do have a clever young friend that had done it in SQL - but I just couldn't give up on this. It is a very common question my workplace. Thanks for all your help.
|
|
IP Logged |
|
|
|
