Find the Overlap Call in the Same Day and Same Customer

[dineshm030]

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.

QlikView Qlik Sense Desktop @sunny_talwar @MayilVahanan 

[ali_hijazi]

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;

[dineshm030]

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. 

[ali_hijazi]

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;

[dineshm030]

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.

[ali_hijazi]

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

[dineshm030]

Hi @ali_hijazi ,

Thanks for your reply.

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

[ali_hijazi]

so you mean by time zone AM or PM?

[ali_hijazi · Accepted solution]

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;

[dineshm030]

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.