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: 
diegodr83
Contributor II
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)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

4 Replies
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
Contributor II
Contributor II
Author

Hi

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

 

Thanks

sunny_talwar

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
Contributor II
Contributor II
Author

Thank you for your help!