Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Checking for previous occurrences within 30 days

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!

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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;

Anonymous
Not applicable
Author

Fantastic! Simple and smart solution - thank you!