Query filter problem

[Tom Patten]

Hi,

I can't figure out how to know if a person has not had a BM for the past 3 days!! You can image the problems this might cause.

The code below tells me who has not had a BM (bm=none) on any one day, but I need to know who hasn't had a BM for all three days. Seems I need to use a ".and." in the filter, but that doesn't work, neither does a "between".

inputf = table.open("Vitals",FILE_RO_SHARED)
query.description = ""
query.order = "PTEID"
query.filter = "BM='None'.and.(T_date=date().or.T_date=date()-2.or.T_date=date()-3).and.unique_key_value()"
query.options = "MU" '"I"
qry=inputf.query_create()

Thanks for taking a look,

~ Tom

[Stan Mathews]

Re: Query filter problem

Quite irregular!


Maybe

query.filter = "(BM='None' .and. (T_date=date() .or. T_date=date()-2 .or. T_date=date()-3)) .and.unique_key_value()"

Added parentheses.

[Tom Patten]

Re: Query filter problem

Nope - they have to have NOT had a BM for 3 days (72 hours). The ".or." says they could have had a BM on any of the three days. Changing the ".or." to ".and." doesn't work.

Hhmmmmm... Am I missing something obvious!

[johnkoh]

Re: Query filter problem

Tom,

I would try ,
change

BM = "+quote("none") +" .and.
between_date(t_date,*lower date=> today-3 day, *higher date => today )

and without the Unique_key_value

[Tom Patten]

Re: Query filter problem

Can you give it to me again in the following format:

query.filter = "(bm='none').and.between_date(t_date,*lower date=> today-3 day, *higher date => today).and.unique_key_value()"

It may be correct, but it gives me a filter error and when I convert it to the above format.

~ Tom

[Tom Patten]

Re: Query filter problem

I tried it without the unique_key_value also.

query.filter = "(bm='none').and.between_date(t_date,*lower date=> today-3 day, *higher date => today)"

And it didn't work.

~ Tom

[Stan Mathews]

Re: Query filter problem

Let's try a little more information. What is t_date? What is the record structure?

And wouldn't it be date(), date()-1, and date()-2?

[Tom Patten]

Re: Query filter problem

t_date is the date of the last BM note. The note (BM) values are: None / Hard / soft / loose

Hope is isn't too much information.

Below is my full script

dim inputf as p
dim count as n

inputf = table.open("Vitals",FILE_RO_SHARED)
query.description = ""
query.order = "PTEID"
'query.filter = "BM='None'.and.T_date>=date()-3.and.unique_key_value()"
'query.filter = "BM='None'.and.(T_date=date().or.T_date=date()-2.or.T_date=date()-3).and.unique_key_value()"
'query.filter = "(BM='None'.and.T_date=date()).or.(BM='None'.and.T_date=date()-1)"
query.filter = "(BM='None' .and. (T_date=date() .or. T_date=date()-1 .or. T_date=date()-2 .or. t_date=Date()-3))" '.and.unique_key_value()"
'query.filter = "BM='none'.and. between_date(t_date,*lower date>= today-3 day, *higher date >= today)"

query.options = "I" '"mu"
qry=inputf.query_create()

count = inputf.records_get()+1

dim choices[count] as c

i = 1
inputf.fetch_first()
while .not. inputf.fetch_eof()
choices[i] = trim(lookupc("F",inputf.pteid,"Fullname","Patients","pteid"))+" - "+dtoc(inputf.t_date)+" - "+inputf.bm 'Trim(inputf.pteid)
inputf.fetch_next()
i = i + 1
end while
sort_array("choices")
var->v_Char = ui_get_list_array("NO BM in any of the past 3 Days", 1,"Choices")
qry.drop()
inputf.close()

~ Tom

[Stan Mathews]

Re: Query filter problem

I think this works in the context of a report filter. Group report on pteid.

Order
pteid+invert(cdate(t_date))

Filter

tablemax("tablename","bm <> 'none' .and. id = "+quote(pteid),"t_date")< date()-2 .and. unique_key_value()

Sort of the inverse. Show unique records where bm is not "none" and its t_date is earlier than 2 days ago.

[Tom Patten]

Re: Query filter problem

Stan

I need this code to fill the array above so I can show it when a button is pushed.

~ Tom

[Tom Cone Jr]

Re: Query filter problem

Tom, I think you're going to have to build a custom function (or script) and use it instead of a query

If the table were sorted by patient id, and then by inverted T_date I can imagine a function that
- steps through the table one record at a time
- if bm today then skip ahead to next patient
- else
- skip forward one record
- bm? if so, skip to next patient
- else
- skip forward to next record
- bm? if so, skip to next patient
- else ' you've found a patient who's gone 3 days, so store the patient id in a list somewhere
- then skip to the next patient

- display the list when the last patient's records have been processed

This approach assumes each patient has a new record for each day, otherwise you must put in some
logic to deal with skipped dates.

[Tom Patten]

Re: Query filter problem

Thanks Tom.

Since I can query to find who did NOT have a BM in any of the last 3 days

Could I not

1. Step through the query doing a record count on the original (unqueried) table and if the Count was>2 then
2. Capture the pteid into a variable
3. show this variable in an array.

But I also plan to create a report using Stan's suggestion. This would also let staff see who they need to watch for and even print the list.

Another day, another opportunity to keep my brain working.

~Tom

[johnkoh]

Re: Query filter problem

Tom

query.filter = "(bm='none').and.between_date(t_date,*lower date=> today-3 day, *higher date => today)"

Sorry, Tom. This code was just for sample with "How or what to do " with code and not active code. You cannot copy and paste into your code. It will not work with it.

another idea for checking your filter,
vFilter = "your filter code "
vReturnList = table.external......("your table","your table fields(one or more","your order field",vFilter) '*see table.external_record.. not full code because not remember.
showvar(vReturnList,"shows your filter result ") '*

[Al Buchholz]

Re: Query filter problem

I would approach this need by using a summary operation to gather the data into a single record that has the indicators for the last 3 days in one record.

Or a crosstab operation to do something similar.

Then build the list from the combined table.

[Stan Mathews]

Re: Query filter problem

I think this is a variation of Tom's idea. Creates a variable with patient id and t_date for patients. You should be able to fill the array from the variable.

'variable to hold record values of patient ids and last none bm dates
lbm_arr_var = ""
tbl = table.open("dataf")
'query for last not none in descending date order by patient
tbl.query_create("T","bm <> 'none'","pteid+invert(cdate(t_date))")
tbl.fetch_first()
tid = tbl.pteid
while .not. tbl.fetch_eof()
	'if the date of the last not none record is four days ago 
	'write the patient id and the next day's date to the variable 
	lbm_arr_var = lbm_arr_var+ crlf()+if(tbl.t_date < date()-3,tbl.pteid + " " + (tbl.t_date+1),"")
'skip over the rest of the records for this patient
	while tbl.id = tid .and. .not. tbl.fetch_eof()
		tbl.fetch_next()
	end while
	tid = tbl.pteid
end while
tbl.close()
msgbox(lbm_arr_var)

You would change to your table name.