many to many mapping

[krish2459]

Hi ,

I'm having a scenario to deal with many to many.

Attched is the sample data.

I need to plot # sites at each location.

I can't explain more beacure It may confuse.

By seeing data you can understand.

Thanks..

[Lisa_P]

There is not a good relationship in the data. Country is the only thing in common and the data doesn't match up.

eg AUSTRALIA 

PHILIPPINES

[krish2459]

Hi,

Please find the attached revised data.

I need bring site ID to the dimension sheet to get event type for site ID's.

Please help me I'm out of Ideas.

Thanks...

[Lisa_P]

I cannot see the relationship between these tables to do that

[Brett_Bleess]

Try reviewing the following Help link, you may be able to find what you need there to figure out how you need to proceed here:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Loading_data.ht...

Regards,
Brett

[Kushal_Chawda]

try using combination of both country and location. But seems like there are lot many mismatches.

Map_site:
mapping LOAD Distinct lower(trim(Country))&lower(trim(Location)) as Key,
[Site ID]
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is [SIte ID]);

Data:
LOAD country,
location,
//lower(trim(country))&lower(trim(location)) as Key2,
applymap('Map_site',lower(trim(country))&lower(trim(location)),'NA') as [Site ID],
latitude,
longitude,
event_type
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is DImension);