topic Re: Re: When to apply lookup on concatenate table in QlikView

When to apply lookup on concatenate table

Fri, 12 Sep 2014 07:47:24 GMT

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

Re: When to apply lookup on concatenate table

tresB — Fri, 12 Sep 2014 07:56:28 GMT

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

Load

Name,

From <> ;

Left Join

Load

Name,

PhNo

<>;

Re: When to apply lookup on concatenate table

Fri, 12 Sep 2014 07:59:04 GMT

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

Re: When to apply lookup on concatenate table

tresB — Fri, 12 Sep 2014 08:01:20 GMT

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

Re: Re: When to apply lookup on concatenate table

Fri, 12 Sep 2014 08:16:00 GMT

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

Re: When to apply lookup on concatenate table

miikkaqlick — Fri, 12 Sep 2014 08:29:27 GMT

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

Re: When to apply lookup on concatenate table

tresB — Fri, 12 Sep 2014 08:31:06 GMT

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

Re: When to apply lookup on concatenate table

Fri, 12 Sep 2014 08:33:26 GMT

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

Re: Re: When to apply lookup on concatenate table

Fri, 12 Sep 2014 08:36:43 GMT

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.

Re: When to apply lookup on concatenate table

miikkaqlick — Fri, 12 Sep 2014 08:40:24 GMT

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

Re: Re: Re: When to apply lookup on concatenate table

tresB — Fri, 12 Sep 2014 08:46:02 GMT

May be like attached sample.

Re: When to apply lookup on concatenate table

Fri, 12 Sep 2014 08:59:27 GMT

Hi tressco

Unfortunately this isn't quite what I am looking for:

I want to be able to create a list box with the names and then be able to see the number of calls and emails that person has sent/received

Thanks

Re: When to apply lookup on concatenate table

tresB — Fri, 12 Sep 2014 09:03:16 GMT

Create a chart with:

dimension: Name

Exp1: Count(callto)

Exp2: Count(callfrom)

Re: Re: When to apply lookup on concatenate table

miikkaqlick — Fri, 12 Sep 2014 09:14:15 GMT

Here. Number of calls by name in chart.

Br,

Miikka

Climber Finland