How to do this??

[ecabanas]

Hi,

I would like to cluster my customers as a "Superloyal" if a Customer bought at minimum once order every month last 3 months.

I have the following table and I would like that the script fill the field "Superloyal" and I have no idea how to do it...

Thank's

Eduard

CUSTACCOUNT_CONTADOR	DATEFINANCIAL_CONTADOR	SuperLoyal
C000002	31/08/2012	No
C000002	31/10/2012	No
C000002	31/12/2012	No
C000002	31/01/2013	No
C000002	28/02/2013	Yes
C000002	31/03/2013	Yes
C000002	30/04/2013	Yes
C000002	31/05/2013	Yes
C000002	31/07/2013	No
C000002	31/08/2013	No
C000002	30/09/2013	Yes
C000014	31/05/2012	No
C000014	31/07/2012	No
C000014	31/08/2012	No
C000014	30/09/2012	Yes
C000014	31/10/2012	Yes
C000014	30/11/2012	Yes
C000014	31/01/2013	No
C000014	28/02/2013	No
C000014	31/03/2013	Yes
C000014	30/04/2013	Yes
C000014	31/05/2013	Yes
C000014	31/07/2013	No
C000014	31/08/2013	No

[Anonymous]

Hi,

I thought that all your dates where month end date but you have all kind of dates, that is why it didn't work.

Try this, it works fine:

FACT_tmp:

LOAD

*,
CUSTACCOUNT_CONTADOR&month(DATEFINANCIAL_CONTADOR)&year(DATEFINANCIAL_CONTADOR) as %KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR;
LOAD * INLINE [
CUSTACCOUNT_CONTADOR, DATEFINANCIAL_CONTADOR
C000014,29/09/2011
C000014,17/11/2011
C000014,20/11/2011
C000014,22/01/2012
C000014,01/04/2012
C000014,11/05/2012
C000014,17/05/2012
C000014,10/07/2012
C000014,21/08/2012
C000014,22/09/2012
C000014,26/10/2012
C000014,17/11/2012
C000014,08/01/2013
C000014,28/02/2013
C000014,18/03/2013
C000014,01/04/2013
C000014,10/04/2013
C000014,12/05/2013
C000014,20/05/2013
C000014,23/05/2013
C000014,01/07/2013
C000014,27/08/2013
C000002,17/10/2011
C000002,28/12/2011
C000002,28/12/2011
C000002,02/08/2012
C000002,17/08/2012
C000002,26/10/2012
C000002,12/12/2012
C000002,22/12/2012
C000002,04/01/2013
C000002,25/01/2013
C000002,15/02/2013
C000002,13/03/2013
C000002,01/04/2013
C000002,19/04/2013
C000002,13/05/2013
C000002,30/05/2013
C000002,04/07/2013
C000002,13/08/2013
C000002,16/09/2013
C000014,29/09/2011

];


JOIN

LOAD

CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)) as DATEFINANCIAL_CONTADOR2,
'1' as Flag

RESIDENT

FACT_tmp
Where
Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&Month(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2))&Year(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)))
;

JOIN

LOAD
CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)) as DATEFINANCIAL_CONTADOR3,
'1' as Flag2

RESIDENT

FACT_tmp
Where

Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&Month(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1))&Year(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)))
;

FACT:
LOAD

CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
if(Flag+Flag2=2,'SuperLoyal') as SuperLoyal

RESIDENT

FACT_tmp;

DROP TABLE FACT_tmp;

[#3]

Eduard:

I did not accumulate the sales. I simply mark each month with an "X" if there were sales or not.

Please check again, because based on your data, it's working as you expect, that is, for the last five dates, only C000002 is considered SuperLoyal in September (it's considered NOT superloyal in august for example, since in June there were no sales).

Awaiting your feedback,

Regards

Rtapia