Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Contributor II

## People who bought for two years in a row

Hi All,

I have a question I'm not able to solve.

I have a db table with people with name and code, date and amount spent, for example

1 | John Doe | code123 | 01/05/2010 | 100\$

2| John Doe | code123 | 01/05/2014 | 150\$

3 | John Smith | code345 |01/05/2011 | 50\$

4 | John Doe | code123 |01/08/2011 | 100\$

5 | Terry Rogers | code123 |01/08/2011 | 100\$

5 | Terry Rogers | code123 |01/08/2014 | 80\$

I need to find if a customer buy items for two consecutive years (no need to be a specific year, I'm analyzing the complete history so in my example Doe has this flag true, Smith and Rogers false).

Any suggestion? Thank you in advance

Labels (1)
• ### QLIKVIEW Script help

1 Solution

Accepted Solutions
MVP

Try this

``````Table:
Year(date) as year;
id, name, code, date, value
1, John Doe, code123, 01/05/2010, 100
2, John Doe, code123, 01/05/2014, 150
3, John Smith, code345, 01/05/2011, 50
4, John Doe, code123, 01/08/2011, 100
5, Terry Rogers, code123, 01/08/2011, 100
5, Terry Rogers, code123, 01/08/2014, 80
];

FinalTable:
If(name = Previous(name) and year = Previous(year)+1, 1, 0) as Flag
Resident Table
Order By name, date;

DROP Table Table;

Left Join (FinalTable)
Max(Flag) as ConsecutiveYearFlag
Resident FinalTable
Group By name;

DROP Field Flag;``````
4 Replies
MVP

Do you want to see John with True, or the specific rows 2010 and 2011 to show True and 2014 as False?

Contributor II
Author

Hi

Just John with true, I don't need to know when the two in a row happened

Thanks

MVP

Try this

``````Table:
Year(date) as year;
id, name, code, date, value
1, John Doe, code123, 01/05/2010, 100
2, John Doe, code123, 01/05/2014, 150
3, John Smith, code345, 01/05/2011, 50
4, John Doe, code123, 01/08/2011, 100
5, Terry Rogers, code123, 01/08/2011, 100
5, Terry Rogers, code123, 01/08/2014, 80
];

FinalTable:
If(name = Previous(name) and year = Previous(year)+1, 1, 0) as Flag
Resident Table
Order By name, date;

DROP Table Table;

Left Join (FinalTable)