Logical Condition

[Venamma23]

Hi Everyone,

I have a scenario where I need to show team change based on the effective date given in the second table.

I need to join these two tables based on Queue and and output is expected as below Output table.
Can anyone please suggest me how to do this?

Note: Its sample data set. In the same way I have multiple queues with multiple teams.

Table1:

Queue	Calls Offered	Timeslot Date
GCS_VOICE	3	01/09/2020
GCS_VOICE	4	02/09/2020
GCS_VOICE	2	03/09/2020
GCS_VOICE	3	04/09/2020
GCS_VOICE	1	05/09/2020
GCS_VOICE	4	06/09/2020
GCS_VOICE	2	07/09/2020
GCS_VOICE	5	08/09/2020
GCS_VOICE	6	09/09/2020

Table2:

Queue	Team	Effective Date
GCS_VOICE	Internal team	01/09/2020
GCS_VOICE	External Team	06/09/2020

Output:

Queue	Calls Offered	Timeslot Date	Team
GCS_VOICE	3	01/09/2020	Internal team
GCS_VOICE	4	02/09/2020	Internal team
GCS_VOICE	2	03/09/2020	Internal team
GCS_VOICE	3	04/09/2020	Internal team
GCS_VOICE	1	05/09/2020	Internal team
GCS_VOICE	4	06/09/2020	External Team
GCS_VOICE	2	07/09/2020	External Team
GCS_VOICE	5	08/09/2020	External Team
GCS_VOICE	6	09/09/2020	External Team

[Taoufiq_Zarra]

@Venamma23  One Loginc in Script:

Table1:
LOAD * INLINE [
   Queue, Calls Offered, Timeslot Date
    GCS_VOICE, 3, 01/09/2020
    GCS_VOICE, 4, 02/09/2020
    GCS_VOICE, 2, 03/09/2020
    GCS_VOICE, 3, 04/09/2020
    GCS_VOICE, 1, 05/09/2020
    GCS_VOICE, 4, 06/09/2020
    GCS_VOICE, 2, 07/09/2020
    GCS_VOICE, 5, 08/09/2020
    GCS_VOICE, 6, 09/09/2020
];

Temp:

Load

        max([Timeslot Date]) as maxDate

Resident Table1;

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

Table2:

LOAD  * INLINE [
    Queue, Team, Effective Date 
    GCS_VOICE, Internal team, 01/09/2020
    GCS_VOICE, External Team, 06/09/2020
]; 

Tmp:

load *,if(rowno()=1,Date('$(varMaxDate)'),peek([Effective Date])) as Enddate resident Table2 order by [Effective Date] DESC ;

drop table Table2;

Tmp2:
noconcatenate

load Queue, Team,if(rowno()<>1,Date([Effective Date]+1),[Effective Date]) as [Effective Date],Enddate resident Tmp order by  [Effective Date];

drop table Tmp;



output:
left join (Table1)

load *,Date([Effective Date] + IterNo() - 1) as [Timeslot Date] resident Tmp2 While [Effective Date] + IterNo() -1 <= Enddate;



drop table Tmp2;
drop fields Enddate,[Effective Date];

output:

to change these parts by your data bases source

Table2:

LOAD * INLINE [
Queue, Team, Effective Date
GCS_VOICE, Internal team, 01/09/2020
GCS_VOICE, External Team, 06/09/2020
];

and

Table1:
LOAD * INLINE [
Queue, Calls Offered, Timeslot Date
GCS_VOICE, 3, 01/09/2020
GCS_VOICE, 4, 02/09/2020
GCS_VOICE, 2, 03/09/2020
GCS_VOICE, 3, 04/09/2020
GCS_VOICE, 1, 05/09/2020
GCS_VOICE, 4, 06/09/2020
GCS_VOICE, 2, 07/09/2020
GCS_VOICE, 5, 08/09/2020
GCS_VOICE, 6, 09/09/2020
];

[Kushal_Chawda]

@Venamma23  try below

Map_team:
mapping LOAD Queue&floor([Effective Date]) as Key, 
     Team
FROM Table2;

Data:
LOAD Queue, 
     ApplyMap('Map_team',Queue&floor([Timeslot Date]),Null()) as Team,
     [Calls Offered], 
     [Timeslot Date]
FROM Table1;

Final:
NoConcatenate
LOAD Queue, 
     if(IsNull(Team),Peek(Team),Team) as Team,
     [Calls Offered], 
     [Timeslot Date] 
Resident Data
Order by Queue,[Timeslot Date] ;

DROP Table Data;

[Venamma23]

Hi @Taoufiq_Zarra 

Thank you very much for replying on my post.

I have gone through your solution and  it worked for that particular condition but I do have data where there was no team switch, It has only just effective date so it should be displaying the same team name for all the dates for that Queue.

Could you please have a look at it and help me please?

Table1:

Queue	Calls Offered	Timeslot Date
GCS_VOICE	3	01/09/2020
GCS_VOICE	4	02/09/2020
GCS_VOICE	2	03/09/2020
GCS_VOICE	3	04/09/2020
GCS_VOICE	1	05/09/2020
GCS_VOICE	4	06/09/2020
GCS_VOICE	2	07/09/2020
GCS_VOICE	5	08/09/2020
GCS_VOICE	6	09/09/2020
GCS_NEW	3	01/09/2020
GCS_NEW	4	02/09/2020
GCS_NEW	2	03/09/2020
GCS_NEW	1	04/09/2020

Table2:

Queue	Team	Effective Date
GCS_VOICE	Internal team	01/09/2020
GCS_VOICE	External Team	06/09/2020
GCS_NEW	Data Team	02/09/2020

1. for the Queue  GCS_VOICE - should display team as "Internal Team" between 01/09/2020 and 06/09/2020 . And after 06/09/2020 it should display as "External Team"
2. GCS_NEW - It has no team switch so whatever the data I have in table1 should be displayed with the team name as "Data Team" from the Effective Date

in the same way I have multiple Queues in both the tables, In table 2 I have Queues which has team switch and also there are queues which have single team and no team switch.

Output:

Queue	Calls Offered	Timeslot Date	Team
GCS_VOICE	3	01/09/2020	Internal team
GCS_VOICE	4	02/09/2020	Internal team
GCS_VOICE	2	03/09/2020	Internal team
GCS_VOICE	3	04/09/2020	Internal team
GCS_VOICE	1	05/09/2020	Internal team
GCS_VOICE	4	06/09/2020	External Team
GCS_VOICE	2	07/09/2020	External Team
GCS_VOICE	5	08/09/2020	External Team
GCS_VOICE	6	09/09/2020	External Team
GCS_NEW	3	01/09/2020	Null
GCS_NEW	4	02/09/2020	Data Team
GCS_NEW	2	03/09/2020	Data Team
GCS_NEW	1	04/09/2020	Data Team

[Taoufiq_Zarra · Accepted solution]

@Venamma23  try below :

Table1:

load Queue as Q, [Calls Offered] as C, [Timeslot Date] as T;
LOAD * INLINE [
    Queue, Calls Offered, Timeslot Date
    GCS_VOICE, 3, 01/09/2020
    GCS_VOICE, 4, 02/09/2020
    GCS_VOICE, 2, 03/09/2020
    GCS_VOICE, 3, 04/09/2020
    GCS_VOICE, 1, 05/09/2020
    GCS_VOICE, 4, 06/09/2020
    GCS_VOICE, 2, 07/09/2020
    GCS_VOICE, 5, 08/09/2020
    GCS_VOICE, 6, 09/09/2020
    GCS_NEW, 3, 01/09/2020
    GCS_NEW, 4, 02/09/2020
    GCS_NEW, 2, 03/09/2020
    GCS_NEW, 1, 04/09/2020
];

table2:

LOAD * INLINE [
    Queue, Team, Effective Date 
    GCS_VOICE, Internal team, 01/09/2020
    GCS_VOICE, External Team, 06/09/2020
    GCS_NEW, Data Team, 02/09/2020
];

TmpTable2:
noconcatenate

load *,if(peek(Queue)=Queue,Date(peek([Effective Date])-1)) as MaxEffectivedate;
load * resident table2 order by Queue,[Effective Date] DESC;

drop table table2;

left join (TmpTable2)

LOAD  Q as Queue, C as [Calls Offered], T as [Timeslot Date]  resident Table1;

Tmp:
noconcatenate

load * resident TmpTable2;

left join load Date(min([Timeslot Date])) as [MinTimeslot Date],Date(Max([Timeslot Date])) as [MaxTimeslot Date],Queue resident TmpTable2 group by Queue;

drop table TmpTable2;


output1:

load Queue,Team, [Calls Offered], [Timeslot Date],[MinTimeslot Date],[Effective Date],if(len(MaxEffectivedate)=0,[MaxTimeslot Date],MaxEffectivedate) as MaxEffectivedate resident Tmp;

drop table Tmp;


Final:
noconcatenate
load Queue,Team, [Calls Offered], [Timeslot Date] resident output1 where ([Timeslot Date]>=[Effective Date] and [Timeslot Date]<=MaxEffectivedate);
right join LOAD  Q as Queue, C as [Calls Offered], T as [Timeslot Date]  resident Table1;

drop table output1,Table1;

output: