aggregations using if clause and distincts

[#1]

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

[jonathandienst · Accepted solution]

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

[#3]

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;

[jonathandienst]

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

[#5]

the data is attached on the original message