3 Replies Latest reply: Dec 19, 2017 1:13 AM by Anand Chouhan RSS

    Comparing with previous id

    aparna v

      HI everyone,

       

      I have account ID's with dates. I want to have active and inactive flag based on dates.

      Active Flag is min(Date) and inactive flag should be max(Date)+1.

      Any suggestions please?

       

      Data is like

      ID    Date(DD/MM/YY)

      1      1/1/2016

      1      2/1/2016

      2       1/1/2016

      1       3/1/2016

      2       2/1/2016

      2       3/1/2016

      3        2/12016

      1       4/1/2016

      2       4/1/2016

       

      The expected output is the count of active and inactive flags for each date

        • Re: Comparing with previous id
          Anand Chouhan

          May be try this

           

          Source:
          LOAD ID,Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY') as Date;
          LOAD * INLINE [
              ID, Date
              1, 1/1/2016
              1, 2/1/2016
              2, 1/1/2016
              1, 3/1/2016
              2, 2/1/2016
              2, 3/1/2016
              3, 2/1/2016
              1, 4/1/2016
              2, 4/1/2016
          ];
          
          
          New:
          LOAD
          ID,
          Date(Min(Date)) as MinDate,
          Date(Max(Date)+1) as MaxDate
          Resident Source
          Group By ID;
          
          
          DROP Table Source;
          
          
          Final:
          LOAD ID, MinDate as Date,
          'Active' as Flag
          Resident New;
          
          
          Concatenate(Final)
          LOAD ID, MaxDate as Date,
          'InActive' as Flag
          Resident New;
          
          
          DROP Table New;
          
          

           

          Output is this

          Image2.PNG

            • Re: Comparing with previous id
              aparna v

              Thanks for helping. What if the inactive flags fall on weekend, I want that to show on nearest monday

                • Re: Comparing with previous id
                  Anand Chouhan

                  And then load this way

                  For Inactive Flags do this way

                   

                  if(WeekDay(MaxDate)='Sat', Date(MaxDate+2),

                  if(WeekDay(MaxDate)='Sun', Date(MaxDate+1),MaxDate)) as Date

                   

                   

                  Source:
                  LOAD ID,Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY') as Date;
                  LOAD * INLINE [
                      ID, Date
                      1, 1/1/2016
                      1, 2/1/2016
                      2, 1/1/2016
                      1, 3/1/2016
                      2, 2/1/2016
                      2, 3/1/2016
                      3, 2/1/2016
                      1, 4/1/2016
                      2, 4/1/2016
                  ];
                  
                  
                  New:
                  LOAD 
                  ID,
                  Date(Min(Date)) as MinDate,
                  Date(Max(Date)+1) as MaxDate
                  Resident Source
                  Group By ID;
                  
                  
                  DROP Table Source;
                  
                  
                  Final:
                  LOAD ID, 
                  MinDate as Date,
                  WeekDay(MinDate) as WDate,
                  'Active' as Flag
                  Resident New;
                  
                  
                  Concatenate(Final)
                  LOAD ID, 
                  //MaxDate as Date,
                  if(WeekDay(MaxDate)='Sat', Date(MaxDate+2),
                  if(WeekDay(MaxDate)='Sun', Date(MaxDate+1),MaxDate)) as Date,
                  WeekDay(MaxDate) as WDate,
                  'InActive' as Flag
                  Resident New;
                  
                  
                  DROP Table New;