topic Re: Conditional multiplication in Qlikview in QlikView

Conditional multiplication in Qlikview

daumkep — Mon, 11 Mar 2019 23:13:38 GMT

Dear all,

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-.

Some examples:
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+
   else:
      score = score*LR-

The output table shall look like this:
A;1.5
B;0.42
C;0.563
...

Does somebody know how to do this and could give me advise on it?

Thanks a lot for your help

Philipp

Re: Conditional multiplication in Qlikview

Shubham_Deshmukh — Tue, 12 Mar 2019 04:59:41 GMT

HI Philipp,
Can you please elaborate this,
Some examples:
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
What is score(A) , score(B) as they are in list box

Re: Conditional multiplication in Qlikview

daumkep — Tue, 12 Mar 2019 08:53:56 GMT

Hi Shubham,

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:

A;1.5
B;0.42
C;0.563

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
Philipp

Re: Conditional multiplication in Qlikview

daumkep — Wed, 13 Mar 2019 08:52:55 GMT

Anybody who could help me with this?

Thanks a lot
Philipp

Re: Conditional multiplication in Qlikview

Shubham_Deshmukh — Wed, 13 Mar 2019 09:02:47 GMT

Sorry Daumkep, I didn't receive your last notification.
Can you share sample data so that I can try this with your data

Re: Conditional multiplication in Qlikview

daumkep — Wed, 13 Mar 2019 10:07:39 GMT

Hi Shubam,

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.

Philipp

Re: Conditional multiplication in Qlikview

Shubham_Deshmukh — Wed, 13 Mar 2019 10:27:38 GMT

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.

Re: Conditional multiplication in Qlikview

Shubham_Deshmukh — Wed, 13 Mar 2019 13:31:59 GMT

Is your LR data fixed?
Is it in excel sheet,if yes does it gone change in future?

Re: Conditional multiplication in Qlikview

daumkep — Wed, 13 Mar 2019 14:30:56 GMT

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.

Re: Conditional multiplication in Qlikview

Shubham_Deshmukh — Fri, 15 Mar 2019 05:59:56 GMT

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-]))

Regards,

Shubham

Re: Conditional multiplication in Qlikview

daumkep — Fri, 22 Mar 2019 13:59:03 GMT

Hi Shubham,

thanks a lot for your help. It's not exactly what I was looking for (in reality, the number of Symptoms are in the order of hundreds), but it was nevertheless helpful. I guess that I might have to calculate the values outside of Qlik in a script and load it afterwards.

Thanks again
Philipp