Count Responses

[Anonymous]

  Hi All

I Got Data (Test Data Below)

Ref	Date	Direction	EmailAddress
1	10/10/2016	o	Test@1.com
1	09/10/2016	i	Test@1.com
2	10/10/2016	o	New@1.Com
2	09/10/2016	i	New@1.Com
2	08/10/2016	i	New@1.Com

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]

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

[Anonymous]

Hi Sunny

The max in this case.

Thanks

[sunny_talwar · Accepted solution]

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]

Thanks buddy for the help!