Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
thuyphuong1
Contributor
Contributor

Synthetic keys when loading different raw data

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

Capture.PNG

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.

Capture2.PNG

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

0 Replies