Forum Discussion

danielc7150's avatar
danielc7150
Frequent Visitor
9 years ago
Solved

Mapping locations with drill-down into sublocations

Hello,

I am currently mapping a few locations of student accomodation - there are about 7 'main' areas.

These areas then drill-down into smaller 'blocks' of accomodation.

I have the coordinates on a different table to my big table of data. The problem is that these blocks are numbered, and because they are numbered they are not distinct from other 'main areas' and so i cannot form a relationship with them to the main table due to them having to be 'one-to-many'.

 

I realise that I could give them a prefix but this would involve massive changes to the original table as the whole table is made using references and frankly that would take a very long time to change.

 

So I was wondering if there was any way to pair the 'main area's with their smaller 'blocks' so that I would not run into this problem.

 

 

 

Thanks,

Dan

  • MalS's avatar
    MalS
    9 years ago

    Hi  danielc7150 - I can get a test to work the way I think you want it to by doing this:

     

    Location

    Coordinates [Site]

    Coordinates [Block]

     

    Latitude 

    Average of Coordinates [Latitude]

     

    Longitude

    Average of Coordinates [Longitude]

     

    Size

    Count of 'Processed Jobs' [Area/Block] <-- note this is from 'Processed Jobs' NOT 'Coordinates. Also, make sure this is set to Count and not Count (Distinct)

     

    I only have one relationship, and that is from Processed Jobs (Area/Block) to Coordinates (Block)

     

    If I set it up like this, the bubbles at the size level are sized based on the total number of 'jobs' for the entire site. When I drill in, the bubbles are sized by the total number of 'jobs' for each block. 

     

     

     

13 Replies

  • MalS's avatar
    MalS
    Icon for Resolver III rankResolver III
    9 years ago

    Do you have a column for 'Main Area' and 'Blocks' in each table? If so, it's pretty easy to merge both columns together in each table using the Query Editor. Then you can create a relationship between the merged columns. 

     

    Just open the Query Editor, select the first table, select the 'Main Area' column, hold down CTRL and select the 'Blocks' column, then on the ribbon click Add Column > Merge Columns. Repeat for the second table. 

    • danielc7150's avatar
      danielc7150
      Frequent Visitor
      9 years ago

      Thanks for the reply.

      This worked, and was able to make the data drill down.

      However, the circle size is relative to a 'count' of entries for each area. When I drill down, this 'count' stays the same for each individual block - it stays the same as the count for the whole 'area'. Any advice on this?

      • MalS's avatar
        MalS
        Icon for Resolver III rankResolver III
        9 years ago

        Glad the merge worked. 

         

        Not sure about the count issue without seeing the data and the relationships, but you probably want your measure to count the entries per block. That way, when you have drilled down it will only show the count per block, and when you drill up it should automatically combine the counts for all the blocks in each area (because there is a relationship between areas and blocks). 

         

        If you are having trouble, perhaps you could post some screenshots of your data and the relationship you have set up.