Synthetic keys when loading different raw data

thuyphuong1 — Tue, 28 Dec 2021 20:18:36 GMT

Hi everyone,

I have a problem when loading data from 2 different raw data files.

Script 1: The synthetic keys are CHOOSE_DATE & PHONE

Report_telesales:
LOAD
"Tickets Channel" AS TICKET_CHANNEL,
TEXT("Contacts Phone 1") AS PHONE,
"Contacts Full Name" AS CONTACT_NAME,
TEXT("Tickets CIF") as CIF_TELESALE,
Timestamp(Timestamp#("Tickets Created Time",'DD-MM-YYYY hh:mm:ss'),'MM/DD/YYYY')AS TICKETCREATEDTIME,
Timestamp(Timestamp#("Tickets Created Time",'DD-MM-YYYY hh:mm:ss'),'MM/DD/YYYY')AS CHOOSE_DATE
FROM [lib://DIGITAL_FOLDER (nor_qlikservices)/Digital Sales/Telesales result/XLS/Report Telesale BC1*.xls]
(biff, embedded labels, table is Worksheet$);

Report_LB:
LOAD
"Tickets Subject" as TICKET_SUBJECT,
Miền AS MIEN,
DATE("Thời gian tiếp nhận") AS TIEPNHAN_DATE,
DATE("Thời gian tiếp nhận") AS CHOOSE_DATE,
DATE("Thời gian gặp KH chốt sales") AS CHOTSALE_DATE,
TEXT("Contacts Phone 1") AS PHONE
FROM [lib://DIGITAL_FOLDER (nor_qlikservices)/Digital Sales/result/RESULT*.xlsx]
(ooxml, embedded labels, table is REPORT);

The result for script 1 as below. There are 2 separated rows for only 1 phone number @@

Script 2: I remove key CHOOSE_DATE in the script.

Report_telesales:
LOAD
"Tickets Channel" AS TICKET_CHANNEL,
TEXT("Contacts Phone 1") AS PHONE,
"Contacts Full Name" AS CONTACT_NAME,
TEXT("Tickets CIF") as CIF_TELESALE,
Timestamp(Timestamp#("Tickets Created Time",'DD-MM-YYYY hh:mm:ss'),'MM/DD/YYYY')AS TICKETCREATEDTIME,
FROM [lib://DIGITAL_FOLDER (nor_qlikservices)/Digital Sales/Telesales result/XLS/Report Telesale BC1*.xls]
(biff, embedded labels, table is Worksheet$);

Report_LB:
LOAD
"Tickets Subject" as TICKET_SUBJECT,
Miền AS MIEN,
DATE("Thời gian tiếp nhận") AS TIEPNHAN_DATE,
DATE("Thời gian gặp KH chốt sales") AS CHOTSALE_DATE,
TEXT("Contacts Phone 1") AS PHONE
FROM [lib://DIGITAL_FOLDER (nor_qlikservices)/Digital Sales/result/RESULT*.xlsx]
(ooxml, embedded labels, table is REPORT);

Result for script 2: All columns are presented in only 1 row for a phone number. This is exactly my expected result.

But I still want to have CHOOSE_DATE as another synthetic key to have another analysis base on date.
Please support me. Thank you in advanced

topic Synthetic keys when loading different raw data in Connectivity & Data Prep

Synthetic keys when loading different raw data