Hi Community,
I need to find active and inactive accounts for sample data like,
ACCOUNT_CODE Date
A 1/Jan/2017
B 1/Jan/2017
C 1/Jan/2017
D 1/Jan/2017
A 2/Jan/2017
B 2/Jan/2017
C 2/Jan/2017
E 2/Jan/2017
I need to find count of active and inactive accounts in each day.Suppose for 2nd Jan the count of active and inactive account is 1-1
(D became inactive and E was active)
I'am using the below code in script but getting wrong counts. Any suggestions??
Temp2:
Load
ACCOUNT_CODE, Date,
if(ACCOUNT_CODE = Previous(ACCOUNT_CODE),
If(Date >= Previous(Date) and (Date - Previous(Date))=1, 'inactive','active'),'new') as TempFlag
Resident accounts
Order By ACCOUNT_CODE,Date;
Drop Table accounts;
NoConcatenate
Final:
Load
*,
If(TempFlag = 'new' and Date > Previous(Date),'inactive',TempFlag) as FinalFlag
Resident Temp2
Order By ACCOUNT_CODE,Date;
