[Gambas-user] Help with converting text in field to number

[Doriano Blengino]

Benoit Minisini ha scritto:
> On lundi 26 janvier 2009, richard terry wrote:
>   
>> As a prefix to this post all contuctive criticism of my flaws/poor logic
>> etc here welcomed (with open arms!). I hope this is not too confusing,
>> which it is likely to be as I don't really know what I'm doing, so its hard
>> to explain, but in essence:
>>
>> I've a table containing my lab results which is as follows and rightly or
>> wrongly I've kept all results initially in a text field as the hl7 I'm
>> dealing with the value field can be numerical or text:
>>
>> CREATE TABLE clin_requests.requests_results
>> (
>>   pk serial NOT NULL,
>>   episode_key integer NOT NULL, -- episode key is unique to a group of
>> results -- all of a fbc
>>   result text,		 -- the actual value eg could be potassium or white cell
>> count "value" text,					 -- could be eg 134 or 1.1 or  text
>>   units text, 					 -- the units for the above
>>   reference_range text,		 -- the reference range
>>   abnormal boolean,			 -- if true the result is abnormal
>>   probability text,				 -- ?currently not used
>>   obx text,		 -- obx= the entire pipe (|) delimited Hl7 row for this result
>>   CONSTRAINT requests_results_pkey PRIMARY KEY (pk)
>> )
>>
>> Now that I can import my data I want to be able to graph it, for example
>> look at the number of patients with hba1c (a diabetic measurement) between
>> certain ranges, dates etc.
>>
>> I pull this information out of a view:
>>
>> I want to end up with a list of data like this by using union queries for
>> each range I want (is there a better way to do this?) which I can pass to
>> the graphing tool
>>
>> xaxes-text|count|display_order
>> -------------------------------------------
>>  5-6             5               1
>>  6-7           100            2
>> over 7       10              3
>>
>> this query:
>>
>> select
>>       '5-6.0' as XAxes_text, count (value) as count,'1' as display_order
>> From clin_requests.vwResults  where fk_lu_request = 872
>>
>> gives for example
>> xaxes-text|count|display_order
>> 6-6.5            100            1
>>
>> But obviously the query needs a bit more as that is all the values in the
>> view, not the specific range I want so  I have to be able to convert the
>> value field to a number and then test if that number is between my ranges.
>>
>> I tried various combinations and permutation of the above base query plus
>> using the function  to_number("value","9D9"), to no avail.
>>
>> It must be a matter of correct syntax.
>>
>> Any help appreciated, plus any help pointing me to any references about
>> graphing values in postgres (besides the doc's which I have - ie a tutoral
>> would be useful.
>>
>> Regards, and thanks in anticipation.
>>
>> Richard
>>
>>     
>
> I just want to suggest the following: add a numeric field in your table so 
> that you store the data both in text form and numeric form. 
>
> When you have to store the result field in a record, you have to convert it to 
> a number and update the numeric field accordingly.
>
> Then your range queries should be easier to do.
>
> Regards,
>
>   
The real, canonical approach would be to create a probably complex 
syntax to code your data. A column should code the name of the 
measurement (say, hba1c), another column should code the type of 
measurement (kind=range, kind=yes-no, kind=textual response...), two 
fields should contain the upper and lower bounds for "normality", 
perhaps a column or two should be used to more precisely identify 
particular out-of-normality values (ie: ranges for recommended values, 
but also ranges identifying perilous values). Such a database should be 
filled by a well-behaved program, using enumeration types whenever 
possible. Note that measurement name and its normal ranges should go in 
a table, and real measurements about patients should go in another table.

This is a burden, but some tradeoff could be used; for example, an 
enumeration for the name of the analysis is the better way, but a text 
string could also work. Perhaps the more important thing to do is to use 
two separate fields for ranges; if you express a range using a single 
textual field, then it is difficult to query the database about that. 
Measurements of kind "yes-no" or "positive-negative" can be simulated by 
a range of "zero to zero", and a positive test could have a vlaue of 1 
(which falls outside the "zero to zero").

I know so little databases and medicine, so take my thought as it is: 
brainstorming.

Cheers and respect for your work,

-- 
Doriano Blengino

"Listen twice before you speak.
This is why we have two ears, but only one mouth."