I'm wondering what order I need to do things in my script.
I'm loading one set of data via SQL Select and then want to apply some lookup fields. I then want to loads a second set of data (again via SQL select) and apply some lookup fields to this (both lookups will come from the same excel document)
I want to concatenate the second table so that the information is loaded below the first table but want some of the lookup fields to be the same across both tables. the expression I have at the moment tells me that field names must be unique.
Unfortunately my script writing knowledge isn't good enough to resolve the issue myself.
Please see below script:
LOAD [Internal Number],
[External Number],
Mobile,
contno,
[Company Name],
[Contact Name],
[Email Address],
cotypedesc,
[Email Company Capture],
Department
FROM
[Contact Database.xlsx]
(ooxml, embedded labels, table is Lookup);
ODBC CONNECT32 TO dbserver_logging;
Email:
SQL SELECT `client-ip`,
date,
`from-account`,
id as emailID,
`message-subject`,
msgid,
`recipient-address`,
`sender-address`,
`to-account`,
`total-bytes`
FROM logging.email;
Concatenate
SQL SELECT accountcode,
answered,
callcost,
callfrom,
callto,
datetime as date,
duration,
id as callID,
phonenumber,
ringtime,
sitecode,
transferred,
typeofcall
FROM logging.phone
Where Not IsNull(accountcode);
Left Join(Email)
Load
*,
if(isnull(Lookup('Contact Name','Internal Number',callfrom,'Lookup')),'Unknown',Lookup('Contact Name','Internal Number',callfrom,'Lookup')) as 'Contact From Name',
if(isnull(Lookup('Contact Name','Internal Number',callto,'Lookup')),'Unknown',Lookup('Contact Name','Internal Number',callto,'Lookup')) as 'Contact To Name',
if(isnull(Lookup('Company Name','External Number',phonenumber,'Lookup')),'Unknown',Lookup('Company Name','External Number',phonenumber,'Lookup')) as 'Company',
if(isnull(Lookup('Contact Name','Email Address',`recipient-address`,'Lookup')),'Unknown',Lookup('Contact Name','Email Address',`recipient-address`,'Lookup')) as 'Contact From Name',
if(isnull(Lookup('Contact Name','Email Address',`sender-address`,'Lookup')),'Unknown',Lookup('Contact Name','Email Address',`sender-address`,'Lookup')) as 'Contact To Name',
if(isnull('emailID'),'Call') as 'Table',
if(isnull('callID'),'Email') as 'Table'
Resident
Email;
Many thanks
Drew
