2 Replies Latest reply: Aug 10, 2011 11:19 AM by kramelot RSS

    How to make some group changes

      Hi Everybody.

       

      Few days ago, i asked the way to do some groups and Mr John Witherspoon gave me the answer as follows:

       

      [User Groups]:
      LOAD
      "User Group"
      ,subfield("Users",',') as "User"
      INLINE [
      User Group: Users
      Group1: User1, User3
      Group2: User2, User4
      ] (delimiter is ':');

       

      It works so good, but now i have a problem, a user changed group, and now i have to change it, but for statistics i need that the user belongs to the first group until a specific date and after pass to the be part of another group,... How do i do this?

       

      Sorry for my english.

       

      Best Regards.

        • Re: How to make some group changes
          John Witherspoon

          You need to add the date to the data structure.  Here's one way:

           

          LET vMinDate = num(makedate(2011,1,1)); // You would establish this from your calendar or data set
          LET vMaxDate = num(makedate(2011,2,28)); // also from your calendar or data set

           

          UserGroups:
          LOAD
          User
          ,date(if(End,End,$(vMaxDate))) as End
          ,date(if(User=previous(User),peek(End)+1,$(vMinDate))) as Start
          ,UserGroup
          INLINE [
          User, End, UserGroup
          User1,,Group1
          User2,,Group2
          User3,1/15/2011,Group1
          User3,2/20/2011,Group2
          User3,,Group3
          User4,,Group2
          ];
          UserGroups2:
          LOAD
          User
          ,date(Start+iterno()-1) as Date
          ,UserGroup
          RESIDENT UserGroups
          WHILE Start+iterno()-1 <= End
          ;
          DROP TABLE UserGroups;