Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can you help me to update the Aggrgate function with Year to Month in Load statement:
1-12 months - AAA
13-36 months - BBB
37-60 months - CCC
if(aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID)=1,'AAA',
if((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))>=2 and ((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))<=3),'BBB',
if((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))>=4 and ((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))<=5),'CCC')))
May be like this
Fact:
LOAD CustID,
Sales_Date,
Year(Sales_Date) as Year,
...
FROM ...;
Left Join (Fact)
LOAD Cust_ID,
If(MaxYear - MaxYear2 = 1, 'AAA',
If(MaxYear - MaxYear2 <= 3, 'BBB',
If(MaxYear - MaxYear2 <= 5, 'CCC'))) as Flag;
LOAD Cust_ID,
Max(Year) as MaxYear,
Max(Year, 2) as MaxYear2
Resident Fact
Group By Cust_ID;
Thanks Sunny for your response, but I'm looking for months
1-12 months - AAA
13-36 months - BBB
37-60 months - CCC
I am not sure what you mean... do you have a sample or sample data you can share? and explain what you need as an output?