Hi all,
Should be a common problem to solve, yet struggling with it.
Collecting blood pressure values in a table named bloodPressure:
bloodPressureId | dateTime | systolic | diastolic | pulse | bloodPressureClassification |
25080000 | 11/10/2023 06:30:00 | 132 | 76 | 77 | ? |
eb4189e7 | 11/10/2023 12:45:00 | 133 | 79 | 73 | ? |
87907831 | 11/10/2023 19:45:00 | 138 | 84 | 75 | ? |
In order classify the measured systolic, diastolic, pulse values together, defined a reference table named bloodPressureRef:
bloodPressureRefId | systolicMin | systolicMax | diastolicMin | diastolicMax | pulseMin | pulseMax | classification |
a33697a6 | 110 | 120 | 80 | 60 | 100 | Normal | |
cbaaf608 | 120 | 129 | 80 | 60 | 100 | Elevated | |
8ff252ba | 130 | 139 | 80 | 89 | 60 | 100 | Hypertension (Stage 1) |
2b2b8150 | 140 | 90 | 60 | 100 | Hypertension (Stage 1) | ||
7abb0c4c | 180 | 120 | 60 | 100 | Hypertension (Emergency) |
I'd like add to the bloodPressure table a virtual column e.g. named bloodPressureClassification, which based on the value ranges defined by
matches the relevant bloodPresuresRef.classification value for each row in the bloodPressure table.
What would be your approach?
Many thanks,
Miklos
Hi Miklos,
First thing is that you need to fill each blank space of your table with values (example).
bloodPressureRefId | systolicMin | systolicMax | diastolicMin | diastolicMax | pulseMin | pulseMax | classification |
a33697a6 | 110 | 119 | 0 | 79 | 60 | 100 | Normal |
cbaaf608 | 120 | 129 | 80 | 89 | 60 | 100 | Elevated |
8ff252ba | 130 | 139 | 90 | 99 | 60 | 100 | Hypertension (Stage 1) |
2b2b8150 | 140 | 179 | 100 | 109 | 60 | 100 | Hypertension (Stage 2) |
7abb0c4c | 180 | 1000 | 110 | 1000 | 60 | 100 | Hypertension (Emergency |
___
That being said, there's something I don't understand, is that the blood pressure in the example don't match any class.
bloodPressureId | dateTime | systolic | diastolic | pulse |
25080000 | 11/10/2023 06:30:00 | 132 (row of hyper tension S1) | 76 (row of normal) | 77 (row of everyclass) |
What happens when your conditions match differents class ? For the example above, will it be hypertension S1 or normal ?
First thing is that you need to fill each blank space of your table with values (example).
The formula I'm thinking of would be something like :
IF(
AND(
AND(
[systolic] >= LOOKUP("Normal", "bloodPressureRef", "classification", "systolicMin"),
[systolic] <= LOOKUP("Normal", "bloodPressureRef", "classification", "systolicMax")
),
AND(
[diastolic] >= LOOKUP("Normal", "bloodPressureRef", "classification", "diastolicMin"),
[diastolic] <= LOOKUP("Normal", "bloodPressureRef", "classification", "diastolicMax")
),
AND(
[pulse] >= LOOKUP("Normal", "bloodPressureRef", "classification", "pulseMin"),
[pulse] <= LOOKUP("Normal", "bloodPressureRef", "classification", "pulseMax")
)
),
"Normal",
IF(
AND(
AND(
[systolic] >= LOOKUP("Elevated", "bloodPressureRef", "classification", "systolicMin"),
[systolic] <= LOOKUP("Elevated", "bloodPressureRef", "classification", "systolicMax")
),
AND(
[diastolic] >= LOOKUP("Elevated", "bloodPressureRef", "classification", "diastolicMin"),
[diastolic] <= LOOKUP("Elevated", "bloodPressureRef", "classification", "diastolicMax")
),
AND(
[pulse] >= LOOKUP("Elevated", "bloodPressureRef", "classification", "pulseMin"),
[pulse] <= LOOKUP("Elevated", "bloodPressureRef", "classification", "pulseMax")
)
),
"Elevated",
IF(
AND(
AND(
[systolic] >= LOOKUP("Hypertension (Stage 1)", "bloodPressureRef", "classification", "systolicMin"),
[systolic] <= LOOKUP("Hypertension (Stage 1)", "bloodPressureRef", "classification", "systolicMax")
),
AND(
[diastolic] >= LOOKUP("Hypertension (Stage 1)", "bloodPressureRef", "classification", "diastolicMin"),
[diastolic] <= LOOKUP("Hypertension (Stage 1)", "bloodPressureRef", "classification", "diastolicMax")
),
AND(
[pulse] >= LOOKUP("Hypertension (Stage 1)", "bloodPressureRef", "classification", "pulseMin"),
[pulse] <= LOOKUP("Hypertension (Stage 1)", "bloodPressureRef", "classification", "pulseMax")
)
),
"Hypertension (Stage 1)",
IF(
AND(
[systolic] >= LOOKUP("Hypertension (Stage 2)", "bloodPressureRef", "classification", "systolicMin"),
[systolic] <= LOOKUP("Hypertension (Stage 2)", "bloodPressureRef", "classification", "systolicMax")
),
"Hypertension (Stage 2)",
IF(
AND(
[systolic] >= LOOKUP("Hypertension (Emergency)", "bloodPressureRef", "classification", "systolicMin"),
[systolic] <= LOOKUP("Hypertension (Emergency)", "bloodPressureRef", "classification", "systolicMax")
),
"Hypertension (Emergency)",
"Unknown Classification"
)
)
)
)
)
Adjustments need to be made, depending on your conditions. Maybe it's more like the class will always be the highest class between diastolic or systolic value of a bloodpressure ?
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |