Problem with tables - many-to-many relation

[#1]

Hello!

I'm having some problems with my tables. I have data of origin and destination and I need to plot two maps: one with origin and other with destination.

This way, if city 1 is a origin (ploted in first map), city 2, 3 and 4 are destinations (ploted in second map). However, city 1 can also be a destination, and city 2, 3 and 4 can also be a origin.

It's something like a many-to-many relation.

Can someone help me?

Thanks!!!

[swuehl]

There could be different solutions based on your data model and complete requirements.

For example, you might consider loading your relations into a following structure

ConnectionID, Location, LocationType

With your data, for example

ConnectionID, Location, LocationType

1, City1, Origin

1, City2, Destination

1, City3, Destination

1, City4, Destination

2, City2, Origin

2, City1, Destination

You probably need to transform your existing table to get above, maybe like

Connections:

LOAD ConnectionID, Origin as Location, 'Origin' as LocationType

FROM YourTable;

CONCATENATE (Connections)

LOAD ConnectionID, Destination as Location, 'Destination' as LocationType

FROM YourTable;

Then you can filter your data by LocationType to show Locations in the two maps.

Hope this helps,

Stefan

[#3]

I will try!

Thank you so much, mate!

[#4]

I should do something like this?

LOAD

    Status as LocationType,

    Latitude,

    Longitude,

    Cidade as Location,

    Estado,

    "Estado siglas",

    País,

    ID

FROM [lib://Desktop/Base de Fluxo de Carga 2.0.xlsx]

(ooxml, embedded labels, table is [Base completa]);

Connections:

LOAD ID, Origem as Location, 'Origem' as LocationType

FROM [lib://Desktop/Base de Fluxo de Carga 2.0.xlsx]

(ooxml, embedded labels, table is [Base completa]);

CONCATENATE (Connections)

LOAD ID, Destino as Location, 'Destino' as LocationType

FROM [lib://Desktop/Base de Fluxo de Carga 2.0.xlsx]

(ooxml, embedded labels, table is [Base completa]);

[swuehl]

Well, not sure. I don't think you should rename Status to LocationType in your first LOAD. What is Status field for?

If your first table LOAD is the dimension table for Locations, and your two other tables are the fact tables, this seems ok to me, on first glance.

Would it be possible that you upload a small sample QVW? Could be mock-up data, of course.

[#6]

Status is the same as LocationType. I am uploading a excel file, if it's okay.

[swuehl]

Seems like your excel table shows already the structure I wanted to create, so there seems no need for an additional transformation.

Column A seem to denote the location type, origin or destination, column D the location name and column H the connection ID.

So you should be able to create the two charts, see attached.

[#8]

Well, but this isn't properly working, right?

I need to select some city of origin and see all the possible destinations points in other map.

[swuehl]

I think it's working right, but you can also add a table with Separate fields for origin and destination locations, maybe like attached.