topic Re: aggregations using if clause and distincts in QlikView

aggregations using if clause and distincts

Mon, 12 Oct 2015 13:28:45 GMT

Hi all,

I'm trying to execute the following script in the load and it fails, any reason?

Attaching the source file also:

ma:

LOAD Date,

MA,

subkey,

invited,

responder

FROM

C:\Users\borisk\Desktop\test.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

aggregate:

Load

Date,

MA ,

if (invited=1, count(subkey),0) as exposures,

if (invited=1, count(distinct subkey),0) as uniqueExposures,

if (responder=1, count(subkey),0) as responders,

if (responder=1, count(distinct subkey),0) as uniqueResponders

Resident ma

group By Date, MA;

Thanks,

Boris

Re: aggregations using if clause and distincts

jonathandienst — Mon, 12 Oct 2015 13:32:06 GMT

I think what you need is:

count(if (invited=1, subkey, 0)) as exposures,

count(distinct if (invited=1, subkey ,0)) as uniqueExposures,

count(if (responder=1, subkey, 0)) as responders,

count(distinct if (responder=1, subkey ,0)) as uniqueResponders

Re: aggregations using if clause and distincts

Mon, 12 Oct 2015 13:57:01 GMT

Attaching th QVW,

something isn't right there. the unique are not calculated correctly, I can't find the reason.

This is the current script:

ma:

LOAD Date,

MA,

subkey,

invited,

responder

FROM

C:\Users\borisk\Desktop\test.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

aggregate:

Load

Date as D,

MA as M ,

count(if (invited=1, subkey, 0)) as exposures,

count(distinct if (invited=1, subkey ,0)) as uniqueExposures,

count(if (responder=1, subkey, 0)) as responders,

count(distinct if (responder=1, subkey ,0)) as uniqueResponders

Resident ma

group By Date, MA;

Re: aggregations using if clause and distincts

jonathandienst — Mon, 12 Oct 2015 14:00:04 GMT

Without data, I cannot say. The script looks OK.

Re: aggregations using if clause and distincts

Tue, 13 Oct 2015 05:27:55 GMT

the data is attached on the original message