Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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

Labels (1)
0 Replies