Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

When to apply lookup on concatenate table

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

13 Replies
MVP
MVP

Re: When to apply lookup on concatenate table

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

Load

          Name,

          Email

From <> ;

Left Join

Load

          Name,

          PhNo

<>;

Not applicable

Re: When to apply lookup on concatenate table

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

MVP
MVP

Re: When to apply lookup on concatenate table

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

Not applicable

Re: Re: When to apply lookup on concatenate table

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
Contributor II

Re: When to apply lookup on concatenate table

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

MVP
MVP

Re: When to apply lookup on concatenate table

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

Not applicable

Re: When to apply lookup on concatenate table

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

Not applicable

Re: Re: When to apply lookup on concatenate table

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
Contributor II

Re: When to apply lookup on concatenate table

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