topic Re: Script Key between tables in QlikView

Script Key between tables

Adamo — Sat, 16 Nov 2024 01:27:29 GMT

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!!!

Re: Script Key between tables

Arthur_Fong — Wed, 22 Jan 2020 01:01:34 GMT

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;

Re: Script Key between tables

Adamo — Wed, 22 Jan 2020 09:40:31 GMT

@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;

Re: Script Key between tables

Arthur_Fong — Thu, 23 Jan 2020 01:30:37 GMT

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;

Re: Script Key between tables

Adamo — Thu, 23 Jan 2020 12:07:01 GMT

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;

Re: Script Key between tables

Arthur_Fong — Thu, 23 Jan 2020 12:09:43 GMT

Remove exit script will do.