topic Re: Problem with tables - many-to-many relation in QlikView

Problem with tables - many-to-many relation

Mon, 03 Aug 2015 11:34:11 GMT

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!!!

Re: Problem with tables - many-to-many relation

swuehl — Mon, 03 Aug 2015 11:58:32 GMT

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

Re: Problem with tables - many-to-many relation

Mon, 03 Aug 2015 12:31:18 GMT

I will try!

Thank you so much, mate!

Re: Problem with tables - many-to-many relation

Tue, 04 Aug 2015 12:00:37 GMT

I should do something like this?

LOAD

Status as LocationType,

Latitude,

Longitude,

Cidade as Location,

Estado,

"Estado siglas",

País,

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]);

Re: Problem with tables - many-to-many relation

swuehl — Tue, 04 Aug 2015 12:06:44 GMT

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.

Re: Problem with tables - many-to-many relation

Tue, 04 Aug 2015 12:11:32 GMT

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

Re: Problem with tables - many-to-many relation

swuehl — Tue, 04 Aug 2015 16:44:09 GMT

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.

Re: Problem with tables - many-to-many relation

Wed, 05 Aug 2015 14:23:00 GMT

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.

Re: Problem with tables - many-to-many relation

swuehl — Wed, 05 Aug 2015 17:17:31 GMT

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