count of 4 consecutive date with respect to id

[kavita25]

hello everyone ,

I have data like this 

zone	emp id	date	count
NORTH 1	23	1/10/2020	1
SOUTH	23	2/10/2020
NORTH 1	23	3/10/2020
SOUTH	23	4/10/2020
SOUTH	56	6/11/2020
SOUTH	23	8/11/2020	2
SOUTH	78	10/12/2020
SOUTH	78	11/12/2020
NORTH 1	78	12/12/2020
NORTH 2	78	13/12/2020
NORTH 3	78	14/12/2020
NORTH 4	78	15/12/2020
NORTH 5	78	16/12/2020
NORTH 5	78	17/12/2020

I want show the count of 4 consecutive date  on basis of emp id .

for eg : emp id 23 is visiting the  zone  continuously  from date  1/10/20 to 4/10/20 so it suppose to give  me count as 1,

simultaneously for emp id 78  is visiting the zone  continuously  from date  10/10/20 to 13/10/20  And from 14/10/20 to 17/10/20  it suppose to give count as 2 because it has  2 count of 4 consecutive days 

Any solution would be appreciated .

 Regards ,

Kavita 

[ArnadoSandoval · Accepted solution]

Hi @kavita25 

I will explain a procedure to resolve your issue within the Load Script; before that, I will introduce some properties the logic is based upon:

I want show the count of 4 consecutive date  on basis of emp id: 

• We disregard the zone when finding 4 consecutive days, so we need to sort the data by [emp id] and [date]
• We introduced a running total on the [emp id] + [date]; It was named [Visits_Days], for ([emp id] = 23) returns 1, 2, 3, 4 on the days 01/10/2020, 02/10/2020, 03/10/2020 and 04/10/2020; returns 1 for the 08/11/2020; and returns 1, 2, 3, 4 for the days 10/10/2020,  11/10/2020, 12/10/2020 and 13/10/2020 ( I added those four days for my testings )
• There are three groups of days [01/10/2020 - 04/10/2020], [08/10/2020 - 08/10/2020] and [10/10/2020 - 13/10/2020]
• We introduced a column [Day_Group_Visits] it counts the groups visits per [emp id], so for ([emp id] = 23) its values are 1, 2, 3
• We know that [Day_Group_Visits] 1 and 3 the [emp id] met his quota of 4 consecutives visits! the column [Day_Group_Result] show the number of times the [emp id] reach his quota, one time for groups 1 and 3
• Another column [Day_Visits] was used to show on the first day of each group the number of visits achieved for that group!

 

The same logic could be implemented if the [emp id] has visits quotes per [zone], it is in the script, but I wont comment on the logic; also, I added some extra records while testing the script, which is shown next:

NoConcatenate

RawData:
Load * Inline [
raw_zone,raw_emp id,raw_date,raw_count 
NORTH 1,23,1/10/2020,1
SOUTH,23,2/10/2020,
NORTH 1,23,3/10/2020,
SOUTH,23,4/10/2020,
SOUTH,56,6/11/2020,
SOUTH,23,8/11/2020, 2
SOUTH,78,10/12/2020,
SOUTH,78,11/12/2020,
NORTH 1,78,12/12/2020,
NORTH 2,78,13/12/2020,
NORTH 3,78,14/12/2020,
NORTH 4,78,15/12/2020,
NORTH 5,78,16/12/2020,
NORTH 5,78,17/12/2020
];

NoConcatenate

WorkData:
Load * Inline [
zone,emp id,date,count
NORTH,23,1/10/2020,1
SOUTH,23,2/10/2020, 
NORTH,23,3/10/2020, 
SOUTH,23,4/10/2020, 
SOUTH,56,6/11/2020, 
SOUTH,23,8/11/2020,2
SOUTH,23,10/11/2020,
SOUTH,23,11/11/2020,
SOUTH,23,12/11/2020,
SOUTH,23,13/11/2020,
SOUTH,78,10/12/2020, 
SOUTH,78,11/12/2020, 
NORTH,78,12/12/2020, 
NORTH,78,13/12/2020, 
NORTH,78,14/12/2020, 
NORTH,78,15/12/2020, 
NORTH,78,16/12/2020, 
NORTH,78,17/12/2020,
NORTH,78,12/11/2020, 
NORTH,78,13/11/2020, 
NORTH,78,14/11/2020, 
NORTH,78,15/11/2020, 
NORTH,78,16/11/2020, 
NORTH,78,17/11/2020,
];

// sort data by emp-zone-date

NoConcatenate

WorkData_ezd:
Load *,
     if(VZone_Records = 1 or 
        [emp id] <> Peek([emp id]) or 
        zone <> Peek(zone) /* or
        date <> Peek(date) + 1 */, 1, 
        if ([emp id] = Peek([emp id]) and
            zone     = Peek(zone)     and
            date     = Peek(date)+1, Peek(Zone_Group), Peek(Zone_Group)+1)) as Zone_Group
;
Load *,
     if(zone     = Peek(zone)     and 
        [emp id] = Peek([emp id]) and
        date     = Peek(date)+1, Num(rangesum(peek(Visits_Zone))) + 1, 1) as Visits_Zone,
     RecNo()  As VZone_Records
Resident WorkData
Order By [emp id], zone, date;

Drop Table WorkData;

NoConcatenate

WorkData_temp:
Load *,
     if(VDays_Records = 1 or [emp id] <> Peek([emp id]), 1, 
        if ([emp id] = Peek([emp id]) and
            date     = Peek(date)+1, Peek(Day_Group_Visits), Peek(Day_Group_Visits)+1)) as Day_Group_Visits
;
Load *,
     if([emp id] = Peek([emp id]) and
        date     = Peek(date)+1, Num(rangesum(peek(Visits_Days))) + 1, 1) as Visits_Days,
     RecNo() As VDays_Records
Resident WorkData_ezd
Order By [emp id], date;

NoConcatenate
WorkData_temp2:
Load *,
     Div(Visits_Zone, 4)   as Achieve_Zone_F,
     Div(Visits_Days, 4)   as Achieve_Day_Group
Resident WorkData_temp;

// NoConcatenate

Map_Max_ZoneDay:
Mapping Load [emp id] &'|'& zone &'|'& Zone_Group,
     Max(Achieve_Zone_F)
Resident WorkData_temp2
Group By [emp id] &'|'& zone &'|'& Zone_Group;

Map_Max_Days:
Mapping Load [emp id] & '|' & Day_Group_Visits,
     Max(Achieve_Day_Group)
Resident WorkData_temp2
Group By [emp id] & '|' & Day_Group_Visits;

NoConcatenate 
WorkData:
Load *,
     if(( VZone_Records  = 1 and Zone_Group_Result>0) or
        ( Visits_Zone    = 1 and 
          Zone_Group_Result > 0), Zone_Group_Result)  as Zone_Visits,
     if(  VDays_Records = 1 or
        ( Visits_Days   = 1 and 
          Day_Group_Result > 0 ), Day_Group_Result)   as Day_Visits
;
Load *,
     ApplyMap('Map_Max_ZoneDay', [emp id] &'|'& zone &'|'& Zone_Group) as Zone_Group_Result,
     ApplyMap('Map_Max_Days',  [emp id] & '|' & Day_Group_Visits)      as Day_Group_Result     
Resident WorkData_temp2;

Drop Table WorkData_ezd;
Drop Table WorkData_temp;
Drop Table WorkData_temp2;

Hope this helps,

[kavita25]

Thank you for the solution.

[florent1]

Wonderful ! Thank you very much @ArnadoSandoval !