I have the following situation:
I have a stable table "Overview" with different values for different symptoms.
I now want to calculate an overall score for each patient. the score shall multiply all LR+/LR- scores of all symptoms of each patient. If a patient has a symptom, it shall multiply LR+, otherwise LR-.
Patient A has symptoms 'Hypertension' and 'Obesity' => Score(A) = 1,126*1,333 = 1.5
Patient B has no symptoms => Score(B) = 0,857*0,5 = 0,42
Patient C has symptom 'Obesity', but no 'Hypertension' = > Score(C) = 1,126 * 0,5 = 0,563
The formula I search for, needs to do something like this:
for eachPatient in Patient:
score(eachPatient) = 1
for symptom in symptoms:
if symptom in PatientSymptoms:
score = score*LR+
score = score*LR-
The output table shall look like this:
Does somebody know how to do this and could give me advise on it?
Thanks a lot for your help
the scores are exactly, what I try to calculate. They are not yet in a listbox, but I try to bring them in one.
I have the table 'Overview' which is so far independent of the listboxes PatientSymptoms and Patient. I try to create another listbox (or something similar) which contains 2 columns, one for the Patients (A, B, C...) and one for the score. The Score shall calculate somehow like this:
=Product(If(Symptom = PatientSymptoms;then LR+;else LR-))
So it shall contain the following numbers:
So for Patient A, who has both Symptoms 'Obesity' and 'Hypertension', it shall multiply 1,126*1,333. For Patient B who has no symptoms, it shall multiply 0,857*0,5. Patient C who has 'Obesity', but no 'Hypertension' 1,126*0,5
I hope it is it easier to understand now? Thank you very much for your help.
All the best
sure. I sitll use the personal Qlikview edition, but with a Server edition you can still open it, right? Enclosed is also the little Excel sheet that I imported. Please let me know if you have any questions on it. Thanks a lot for you help.
NO I cannot open tha file but let me try.
Also in Prevalence tab you don't have data of LR , so I will add that data as per your snapshot.
Yes, it is pretty much fixed (at least there is no drilldown when you click on patients. The values Apriori, Sensitivity, Specificity, LR+ and LR+ are computed based on the 'Prevalence' attribute, but that's pretty it.
Hi @daumkep ,
Sorry for late reply, I was working on your data.
You need to connect both data to make them work together like this,
AA: LOAD Patient, Symptom FROM [C:\Users\KT3028\Downloads\DemoData.xlsx] (ooxml, embedded labels, table is Symptoms); join(AA) BB: LOAD Symptom, Prevalence, [LR+], [LR-] FROM [C:\Users\KT3028\Downloads\DemoData.xlsx] (ooxml, embedded labels, table is Prevalence);
Then data will be like this,
I am unable to make it in list box or table but able to make it in different text boxes but I think it is not correct as patient number will be dynamic.
Hope this will help you at least up to some extent.
='A - '& only(if(Symptom='Obesity',[LR+]))*only(if(Symptom='Hypertension',[LR+]))
='B - '& (if(isNull(Symptom),only(if(Symptom='Obesity',[LR-]))*only(if(Symptom='Hypertension',[LR-]))))
='C - '& only(if(Symptom='Obesity',[LR+]))*only(if(Symptom='Hypertension',[LR-]))