Script Key between tables

[Adamo]

I have two tables on the Script:

Telehone_table:

load

Telephone,

Campaing_code,

Campaing_descrip,

Campaing_start_date,

Campaing_end_date

Telephones_records:

Telephone,

Date,

Calls,

Sales

The most obvious will be to do  use Telephone as KEY but  the problem here is that Telephones are reused over time for new campaings, so there is a list of about 50 telephones whereas they are reused for Campaings over time. How to link those tables by a convination of Telephone, Campaing_start_date and Campaing_end_date?

Here is a small sample of each table

Telephone_table:

Telephone,       Campaing_code,      Campaing_descrip,     Campaing_start_date,     Campaing_end_date

1001 (*)                    001                                 Campaing1                      01/01/2019                            15/01/2019

1001                     002                                  Campaing2                      16/01/2019                             23/01/2019

.....                           ......

Note (*): Watch that the same telephone number 1001 is reused for Campaing1 and Campaing2

Telephone_records:

Telephone,           Date,                      Calls,           Sales

1001                       01/01/2019          20                   3

1001                       02/01/2019          25                   6

.....                            .....

Thanks in advance!!!

[Arthur_Fong · Accepted solution]

Try intervalmatch:

Telephone_records:
load Telephone,DATE#(Date,'DD/MM/YYYY')AS Date,Calls,Sales inline [
Telephone,Date,Calls,Sales
1001,01/01/2019,20,3
1001,02/01/2019,25,6
];

Telephone_table:
load Telephone,Campaing_code,Campaing_descrip,date#(Campaing_start_date,'DD/MM/YYYY') AS Campaing_start_date,DATE#(Campaing_end_date,'DD/MM/YYYY')AS Campaing_end_date inline [
Telephone,Campaing_code,Campaing_descrip,Campaing_start_date,Campaing_end_date
1001(*),001,Campaing1,01/01/2019,15/01/2019
1001,002,Campaing2,16/01/2019,23/01/2019
];

inner join
IntervalMatch(Telephone,Date)
load Telephone,Campaing_start_date,Campaing_end_date
Resident Telephone_table;
EXIT script;

[Adamo]

@Arthur_Fong 

Thanks a lot for this, it seems it is exactly what I was looking for. But there is some error on the Intervalmatch load statement.

I managed to make that work changing the last part of your code, it works, but it produces a Shyntetic Key.

Inner Join IntervalMatch (Date,Telephone)
load
Campaing_start_date,
Campaing_end_date,
Telephone
Resident Telephone_table;
EXIT script;

[Arthur_Fong]

Try adding a left join below the intervalmatch statement:

Inner Join IntervalMatch (Date,Telephone)
load
Campaing_start_date,
Campaing_end_date,
Telephone
Resident Telephone_table;

left join(Telephone_records)
load * resident Telephone_table;

drop table Telephone_table;

EXIT script;

[Adamo]

Still produces a Synthetic Key

Telephone_records:
load
Telephone,
DATE#(Date,'DD/MM/YYYY')AS Date,
Calls,
Sales
inline [
Telephone,Date,Calls,Sales
1001,01/01/2019,20,3
1001,02/01/2019,25,6
];

Telephone_table:
load 
Telephone,
Campaing_code,
Campaing_descrip,
date#(Campaing_start_date,'DD/MM/YYYY') AS Campaing_start_date,
DATE#(Campaing_end_date,'DD/MM/YYYY')AS Campaing_end_date 
inline [
Telephone,Campaing_code,Campaing_descrip,Campaing_start_date,Campaing_end_date
1001,001,Campaing1,01/01/2019,15/01/2019
1001,002,Campaing2,16/01/2019,23/01/2019
];

Inner Join IntervalMatch (Date,Telephone)
load 
Campaing_start_date,
Campaing_end_date,
Telephone
Resident Telephone_table;
EXIT script;


left join(Telephone_records)
load * resident Telephone_table;

drop table Telephone_table;

[Arthur_Fong]

Remove exit script will do.