1 Reply Latest reply: Dec 6, 2017 12:29 AM by aparna v RSS

    To find active and inactive account

    aparna v

      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;