0 Replies Latest reply: Sep 11, 2014 4:37 AM by Drew Lees RSS

    concatenate two table load with lookups on both tables

    Drew Lees

      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