topic Re: How to remove duplicates in a set analysis? Thanks! in QlikView

How to remove duplicates in a set analysis? Thanks!

Thu, 29 Nov 2012 20:30:16 GMT

Hello everybody,

In a data cube we use there are duplicated records (same respondents correspond to different question properties)

I used the following expression:

avg({$<X.QID = {$(=only(X_LIST.QID))}, Y.QID = {$(=only(Y_LIST.QID))}, RESPONDENT = {">0"} >} Y.Answer) - original formula

The average works perfectly, but when I need to weight the data, I get the result multiplied by 4 (because of the duplicates)

This is the formula I need to modify:

Sum({$<X.QID = {$(=only(X_LIST.QID))}, Y.QID ={$(=only(Y_LIST.QID))}, %RESPONDENT = {">0"} >} Y.Answer*WEIGHT)/

Sum({$<X.QID = {$(=only(X_LIST.QID))}, Y.QID ={$(=only(Y_LIST.QID))}, %RESPONDENT = {">0"} >} RespondentCount*WEIGHT)

How can I set up DISTINCT RESPONDENT or just do not consider duplicates in the set analysis?

When I use Average function with weighting, I do not get the right results.

Thank you very much in advance!!!!!

jvitantonio — Fri, 30 Nov 2012 09:36:33 GMT

Did you try:

Sum({$<X.QID = {$(=only(X_LIST.QID))}, Y.QID ={$(=only(Y_LIST.QID))}, %RESPONDENT = {">0"} >} distinct Y.Answer*WEIGHT)/

Sum({$<X.QID = {$(=only(X_LIST.QID))}, Y.QID ={$(=only(Y_LIST.QID))}, %RESPONDENT = {">0"} >} distinct RespondentCount*WEIGHT)

Fri, 30 Nov 2012 14:15:44 GMT

I did, but in this case I select only 1 possible Y.Answer which is a scale from 0 to 10, not a respondent.

Tne result is wrong, but thank you anyway.