People who bought for two years in a row

[diegodr83]

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

[sunny_talwar]

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

[diegodr83]

Hi

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

Thanks

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD *,
	 Year(date) as year;
LOAD * INLINE [
    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:
LOAD *,
	 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)
LOAD name,
	 Max(Flag) as ConsecutiveYearFlag
Resident FinalTable
Group By name;

DROP Field Flag;

[diegodr83]

Thank you for your help!