Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

concatenate two table load with lookups on both tables

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

Community Browser