Flag cust ID by date in load statement

[karan_kn]

Please help me to flag old customer by year. attached qvw for your rerence.

CustID	Date	Flag
1	12/7/2015
2	1/25/2016
3	1/24/2017
4	2/16/2017
6	3/3/2017
7	11/7/2017
2	01/02.2018	' < 2 year Old Customer'
9	01/07.2018	New Customer
4	01/11.2018	'<1 Year Old Customer'

Inventry.xlsx

[sunny_talwar]

Do you just need 3 flags or there are multiple flags like ❤️ years, <4 years, <10 years.... etc?

[karan_kn]

Thanks for response Sunny, I need 3 flags  like <=1 year, >=2 and <=3 year, >=4 and <=5 year, and New CustID

[sunny_talwar]

Isn't this the same thing as to what we were trying to do here?

Aggregate function year to month

I am not able to understand what you are trying to do and what issues you are running into. I think you need to elaborate and possible provide more details Karan

[karan_kn]

Yes,  the same. it would be useful aggregate by month

[sunny_talwar]

I see what you mean... try this

Fact:

LOAD CustID,

     Sales_Date,

     (Year(Sales_Date)*12) + Month(Sales_Date) as MonthYear,

     ...

FROM ...;

Left Join (Fact)

LOAD Cust_ID,

     If(MaxMonthYear- MaxMonthYear2 = 12, 'AAA',

     If(MaxMonthYear- MaxMonthYear2 <= 36, 'BBB',

     If(MaxMonthYear- MaxMonthYear2 <= 60, 'CCC'))) as Flag;

LOAD Cust_ID,

     Max(MonthYear) as MaxMonthYear,

     Max(MonthYear, 2) as MaxMonthYear2

Resident Fact

Group By Cust_ID;

[karan_kn]

Is it possible to Flag new CusID's? Ex: CustID: 9, it's new ID, so flag to 'New Customer'

CustID	Date	Flag
1	12/7/2015
2	1/25/2016
3	1/24/2017
4	2/16/2017
6	3/3/2017
7	11/7/2017
2	01/02.2018	' < 2 year Old Customer'
9	01/07.2018	New Customer
4	01/11.2018	'<1 Year Old Customer'