topic Re: Count Responses in QlikView

Count Responses

Anonymous — Mon, 10 Oct 2016 14:48:01 GMT

Hi All

I Got Data (Test Data Below)

I want to count the responses - so where direction is 'o' and Date is greater than Date with direction of 'i'

Any ideas how to do this in script?

Thanks

sunny_talwar — Mon, 10 Oct 2016 14:51:24 GMT

Since Ref 2 has two row so data with direction i, which date do we compare to? What is the expected output here?

Anonymous — Mon, 10 Oct 2016 14:52:56 GMT

Hi Sunny

The max in this case.

Thanks

sunny_talwar — Mon, 10 Oct 2016 14:57:42 GMT

Is this how you envision your final output?

Script

Table:

LOAD Ref,

Date,

Direction,

EmailAddress

FROM

[https://community.qlik.com/thread/235827]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD Ref,

Max(Date) as Max_i_Date

Resident Table

Where Direction = 'i'

Group By Ref;

FinalTable:

LOAD Ref,

Date,

Direction,

EmailAddress,

If(Direction = 'o' and Date > Max_i_Date, 1, 0) as Flag

Resident Table;

DROP Table Table;

Anonymous — Mon, 10 Oct 2016 15:15:50 GMT

Thanks buddy for the help!