When to apply lookup on concatenate table

[#1]

I am wondering at what point should I apply lookup formula on a concatenated table?

I have two tables, one containing call information, one containing email information.

I want to apply a lookup on the phone number and the email address so that a name appears in one column. How would I go about doing this? Do I need to concatenate the tables and then apply the lookup or apply the look up separately to both tables and then concatenate?

Thanks,

Drew

[tresesco]

What is the Key field, Name? If so, you can simply join the tables like:

Load

          Name,

          Email

From <> ;

Left Join

Load

          Name,

          PhNo

<>;

[#3]

at the moment there isn't a key field name - I need to create one.

Also I don't want to left join the data, I want to outer join it. Thank you for your help.

Drew

[tresesco]

Could you share a sample set of data for both the tables?

[#5]

Attached is some sample data

The Calls and Emails tabs are examples of what the data looks like and both come from an ODBC connection.

The lookup table I have is just an Excel document which I load in the script before anything else.

The desire is to concatenate the two tables with the Names being the common link between the two tables so that a filter can be applied but the call and email information is not linked other than the person who has sent / received the calls/emails

Many thanks for your help.

Drew

[miikkaqlick]

Hi!

You said that there is no key-field and you must create it. Field1 in first table would be that and field2 on second.

Information:

Load

     Field1 as Name,

     Telephone

From ...

Outer Join (Information)

Load

     Field2 as Name,

     Email

From ...

Br,

Miikka

Climber Finland

[tresesco]

There has to be a link between Call table and Email table, I don't see one.

[#8]

Hi Miikka,

Unfortunately I do not have name information in either of the databases. Field1 in your example is literally just a number and Field2 is an email address.

I know which number relates to which person and which email address relates to which person - but all this information is kept on a separate table.

Maybe it is possible just to create links? But I think there is complication if I create two links to the same table?

Thanks,

Drew

[#9]

No there isn't one.

The link would be this lookup column.

Attached is how I would do it in Excel.

I want to apply a Vlookup to both tables and use that field as the common data.

[miikkaqlick]

Look:

LOAD [Internal Number],

     Name,

     Email

FROM

(ooxml, embedded labels, table is [lookup table]);

Email:

LOAD date,

     [sender-address] as Email,

     [recipient-address],

     MsgId

FROM

(ooxml, embedded labels, table is [Email Data]);

Call:

LOAD id,

     datetime,

     sitecode,

     typeofcall,

     callfrom,

     callfrom as [Internal Number],

     callto,

     ringtime,

     duration,

     answered,

     transferred,

     phonenumber,

     accountcode,

     callcost,

     Date,

     Hour,

     1 as fl_out,

     0 as fl_in

FROM

(ooxml, embedded labels, table is [Call Data])

WHere callfrom <> '1'

;

LOAD id,

     datetime,

     sitecode,

     typeofcall,

     callfrom,

     callto,

     callto as [Internal Number],

     ringtime,

     duration,

     answered,

     transferred,

     phonenumber,

     accountcode,

     callcost,

     Date,

     Hour,

     0 as fl_out,

     1 as fl_in    

FROM

(ooxml, embedded labels, table is [Call Data])

WHere callfrom = '1'

;

This gives you all information. You can after this combine tables if you want.

Br,

Miikka

Climber Finland