topic Re: Find the Overlap Call in the Same Day and Same Customer in App Development

Find the Overlap Call in the Same Day and Same Customer

dineshm030 — Fri, 15 Nov 2024 20:58:00 GMT

Hi All,

I need to display the overlap call on same day and same customer with different positionid. For example, we have four calls for two days. But we need to display the overlap call (light orange highlighted) in the output.

One positionid started the call with the customer at 9:30 AM whereas different position cannot start the call with same customer. We need to find this type of scenarios.

Thanks in advance.

@sunny_talwar @MayilVahanan

Re: Find the Overlap Call in the Same Day and Same Customer

ali_hijazi — Tue, 21 May 2024 14:03:04 GMT

in the script sort your data by customer, and ScheduleStart in ascending order
the using a preceeding load check if previous customer is the current customer and position id <> previous position id then mark the row with 1 otherwise 0:
and your rows will be the ones where desired_row = 1

source:

LOAD

ActivityID,

ScheduleStart,

ScheduleEnd,

PositionId,

"Customer ID",

Team

FROM [lib://TECHNICAL STORE QVDS/APP DATA/Overlap.xlsx]

(ooxml, embedded labels, table is Sheet1);

sorted_source:

NoConcatenate

Load *

,if(RowNo() <> 1 and Previous("Customer ID") = "Customer ID" and Previous(PositionId) <> PositionId, 1,0) as desired_row;

Load *

Resident source

order by "Customer ID",ScheduleStart asc;

drop table source;

Re: Find the Overlap Call in the Same Day and Same Customer

dineshm030 — Tue, 21 May 2024 14:09:21 GMT

Hi @ali_hijazi ,

Thanks for your response. We need to display only EEE & GGG row because the activities have been done same customer on same day that between the first call of scheduled start and scheduled end.

Re: Find the Overlap Call in the Same Day and Same Customer

ali_hijazi — Tue, 21 May 2024 16:18:28 GMT

source:

LOAD

ActivityID,

ScheduleStart,

ScheduleEnd,

PositionId,

"Customer ID",

Team

FROM [lib://TECHNICAL STORE QVDS/APP DATA/Overlap.xlsx]

(ooxml, embedded labels, table is Sheet1);

sorted_source:

NoConcatenate

Load *

,if(RowNo()=1 or "Customer ID" <> Previous("Customer ID"),1,0) as initiator_row

,if(RowNo() <> 1 and num(DayStart(ScheduleStart)) = Previous(num(DayStart(ScheduleStart))) and Previous("Customer ID") = "Customer ID" and Previous(PositionId) <> PositionId, 1,0) as desired_row;

Load *

Resident source

order by "Customer ID",ScheduleStart asc;

drop table source;

intiator_mapping:Mapping

Load "Customer ID" & '|' & num(daystart(ScheduleStart)) as key

,188 as ok

Resident sorted_source where desired_row = 1;

final_source:

NoConcatenate

Load

ActivityID,

ScheduleStart,

ScheduleEnd,

PositionId,

"Customer ID",

Team

,initiator_row

,if( IsNull(ApplyMap('intiator_mapping',"Customer ID" & '|' & num(daystart(ScheduleStart)),Null())),0,1) as desired_row

// ,ApplyMap('intiator_mapping',"Customer ID" & '|' & num(daystart(ScheduleStart)),Null()) as test

Resident sorted_source;

drop table sorted_source;

Re: Find the Overlap Call in the Same Day and Same Customer

dineshm030 — Wed, 22 May 2024 02:39:58 GMT

Hi @ali_hijazi

AAA and CCC should not come in the output. These calls have been done in the same day with same customer but different time zone. We need to find the overlap call within the time range.

Re: Find the Overlap Call in the Same Day and Same Customer

ali_hijazi — Wed, 22 May 2024 03:10:53 GMT

How do you know the time zone in each row?

What is the time range?

Everytime I suggest a solution you give me additional conditions

Re: Find the Overlap Call in the Same Day and Same Customer

dineshm030 — Wed, 22 May 2024 07:22:49 GMT

Hi @ali_hijazi ,

Thanks for your reply.

I clearly explained in the first message itself orange highlighted records to be displayed.

Re: Find the Overlap Call in the Same Day and Same Customer

ali_hijazi — Wed, 22 May 2024 07:55:09 GMT

so you mean by time zone AM or PM?

Re: Find the Overlap Call in the Same Day and Same Customer

ali_hijazi — Wed, 22 May 2024 08:10:04 GMT

Hello @dineshm030
the following script should solve your requirement

source:

LOAD

ActivityID,

ScheduleStart,

ScheduleEnd,

PositionId,

"Customer ID",

Team

FROM [lib://TECHNICAL STORE QVDS/APP DATA/Overlap.xlsx]

(ooxml, embedded labels, table is Sheet1);

sorted_source:

NoConcatenate

Load *

,if(RowNo()=1 or "Customer ID" <> Previous("Customer ID"),1,0) as initiator_row

,if(RowNo() <> 1 and AMPM = previous(AMPM) and num(DayStart(ScheduleStart)) = Previous(num(DayStart(ScheduleStart))) and Previous("Customer ID") = "Customer ID" and Previous(PositionId) <> PositionId, 1,0) as desired_row;

Load *

,right(Time( ScheduleStart, 'hh:mm:ss TT'),2) as AMPM

Resident source

order by "Customer ID",ScheduleStart asc;

drop table source;

intiator_mapping:Mapping

Load "Customer ID" & '|' & num(daystart(ScheduleStart)) & '|' & right(Time( ScheduleStart, 'hh:mm:ss TT'),2) as key

,188 as ok

Resident sorted_source where desired_row = 1;

final_source:

NoConcatenate

Load

ActivityID,

ScheduleStart,

ScheduleEnd,

PositionId,

"Customer ID",

Team

,initiator_row

,if( IsNull(ApplyMap('intiator_mapping',"Customer ID" & '|' & num(daystart(ScheduleStart)) & '|' & right(Time( ScheduleStart, 'hh:mm:ss TT'),2),Null())),0,1) as desired_row,

right(Time( ScheduleStart, 'hh:mm:ss TT'),2) as AMPM

// ,ApplyMap('intiator_mapping',"Customer ID" & '|' & num(daystart(ScheduleStart)),Null()) as test

Resident sorted_source;

drop table sorted_source;

Re: Find the Overlap Call in the Same Day and Same Customer

dineshm030 — Thu, 23 May 2024 15:52:43 GMT

Hi @ali_hijazi ,

Thanks for your response. We achieved it with your wonderful script. I tried with one more record which is getting in the output.

Thank so much for your assistance.

Re: Find the Overlap Call in the Same Day and Same Customer

ali_hijazi — Wed, 29 May 2024 13:16:20 GMT

@dineshm030 Anytime
I'm glad that I was able to help you
and sorry for any "rude" words 🙂

keep in touch