Compare a value to multiple value ranges

[MiklosVigh]

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

• systolicMin - systolicMax
• diastolicMin - diastolicMax
• pulseMin - pulseMax

matches the relevant bloodPresuresRef.classification value for each row in the bloodPressure table.

What would be your approach?

Many thanks,

Miklos

[baba_sawane]

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 ?