Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist

## count of 4 consecutive date with respect to id

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

Labels (1)
• ### count of 4 consecutive date with respect to id

1 Solution

Accepted Solutions
Specialist II

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:
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:
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:
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
;
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:
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
;
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:
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:
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
;
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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
3 Replies
Specialist II

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:
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:
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:
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
;
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:
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
;
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:
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:
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
;
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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Partner - Specialist
Author

Thank you for the solution.

Contributor III

Wonderful ! Thank you very much @ArnadoSandoval !