Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the Key field, Name? If so, you can simply join the tables like:
Load
Name,
From <> ;
Left Join
Load
Name,
PhNo
<>;
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
Could you share a sample set of data for both the tables?
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
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,
From ...
Br,
Miikka
Climber Finland
There has to be a link between Call table and Email table, I don't see one.
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
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.
Look:
LOAD [Internal Number],
Name,
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