Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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,
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:
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,
Thank you for the solution.
Wonderful ! Thank you very much @ArnadoSandoval !