topic Re: difficult question/remove duplicates in QlikView

difficult question/remove duplicates

drohm002 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have two fields, fieldX with values of 1 and 0, and fieldY with values of 1 and 0.

I want to count how many instances a patient had X.

I want to count how many instances a patient had Y.

And I want to count how many instances a patient had X and Y.

Any ideas?

Thank you

Re: difficult question/remove duplicates

drohm002 — Fri, 23 Mar 2018 18:17:29 GMT

My problem is, I keep double counting the variables and i need help not to do that....I want to create a variable that gives me one number, for those three instances above!

Re: difficult question/remove duplicates

ychaitanya — Fri, 23 Mar 2018 18:25:35 GMT

Have you tried normal set analysis

Count( {<FieldX= {"1"}>}ID) to calculate no.of instances of X

Count( {<FieldY= {"1"}>}ID) to calculate no.of instances of Y

Count( {<FieldX= {"1"}, FieldY={"1"}>}ID) to calculate no.of instances of X and Y

Re: difficult question/remove duplicates

MarcoWedel — Fri, 23 Mar 2018 19:03:56 GMT

Hi,

maybe some solutions could be:

tabPatient:
LOAD RecNo() as PatientID,
    Round(Rand()) as X,
    Round(Rand()) as Y
AutoGenerate 30;

tabFieldTemp:
CrossTable (Field,FieldValue)
LOAD PatientID,
    X,
    Y
Resident tabPatient;

tabField:
LOAD PatientID,
    Field
Resident tabFieldTemp
Where FieldValue=1;

DROP Table tabFieldTemp;

hope this helps

regards

Marco