topic Re: Checking for previous occurrences within 30 days in QlikView

Checking for previous occurrences within 30 days

Anonymous — Tue, 26 Sep 2017 12:27:36 GMT

Hi all,

This is my challenge (simplified):

I have an ID field and a Date field.

For each row I need to check if there is at least 1 occurrence with the same ID within 30 days and flag this with a '1' in a new field called 'Repetition'.

ID DATE REPETITION

A 2017-15-08

A 2017-16-08 1

A 2017-20-08 1

A 2017-30-09

A 2017-19-10 1

How can I achieve this?

Thanks in advance!

Re: Checking for previous occurrences within 30 days

sunny_talwar — Tue, 26 Sep 2017 12:32:51 GMT

In the script? Try this

Table:

LOAD * INLINE [

ID, DATE

A, 2017-15-08

A, 2017-16-08

A, 2017-20-08

A, 2017-30-09

A, 2017-19-10

];

FinalTable:

LOAD *,

If(ID = Previous(ID) and DATE - Previous(DATE) < 30, 1) as REPETITION

Resident Table

Order By ID, DATE;

DROP Table Table;

Re: Checking for previous occurrences within 30 days

Anonymous — Tue, 26 Sep 2017 14:02:09 GMT

Fantastic! Simple and smart solution - thank you!