Look up

[Tool_Tip]

Dear experts

I have following excel data loaded into qliksense:

ExcelData:
LOAD
Id
Name,
Region
FROM [lib://Files /MyFiles/003).xlsx];

Now for each of the ID from this excel we want to display its respective mobile from database table says " Contact"

So we load db table as 

Contact:

Load

ID,

Mobile,

Email

from contact.qvd;

So generally we do left join between these table based on id field and get the result.

Is there any other way we can achive this useing applymap or lookup?

Note- there are more than 2 columns in each of the table not just 2.

[rubenmarin1]

Hi, you can use any of those options, the best solution may be based on the data on the desired outcome.

In example, can be differnt mobiles for the same ID? and if there are many different mobiles you want to show only one or all of them?

You can also a 'group by' with a concat to have all different data from the same id in a single row.

Even in some situations it will be better to load 2 different tables to avoid unwanted duplications.

I think it will be better if you write the issue you have found and why you want to use another option.

[Tool_Tip]

Dear Rub,

Please consider here, one ID, one mobile not multiple.

[lennart_mo · Accepted solution]

Hi @Tool_Tip,

ApplyMap works, as long as the data from contact.qvd is distinct.

A script might look something like this:

Mobile_Map:
Mapping Load
ID,
Mobile
from contact.qvd;

ExcelData:
LOAD
Id
Name,
Region,
ApplyMap('Mobile_Map', Id, 'N.A:') as Mobile
FROM [lib://Files /MyFiles/003).xlsx];

A few important things to remember, when using a mapping load: 

1. The table will automatically be dropped and not show in your data model,

2. Your mapping table must exist of two fields, so if you wanted to map the Emails you'd need a second mapping table.

3. The field used as a mapping criteria must always be loaded first in the mapping table.

Hope this helps!

Best regards
Lennart