Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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

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

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

Contributor II
Contributor II

Fantastic! Simple and smart solution - thank you!