Using Qlik Sense to find my dream home - Part 2

[Edy_Tan]

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you how to geocode an address in the HDB transaction into their latitude longitude coordinate for further Geo Operation and how to combine multiple HDB blocks into bins 

After I have imported the HDB resale transactions, I realized that I cannot plot the location of each HDB block onto the map.  I will need to do a lookup to provide a latitude longitude coordinate for each HDB block.

First I will need to find out all the distinct HDB block from the dataset.  I created a column HDBID that takes in block and street name to form an ID.  The country Singapore is added into the ID to ensure that the lookup is based on address in Singapore.  Go into the data manager and create a calculated field with the formula block&’ ‘&street_name&’ Singapore’

Looking at the street name I noticed` there are short form NTH, STH and C’WEALTH.  These words may not be fully interpreted in the lookup so I will have to rename these words to its full word.

Let me create another table with a distinct list of all HDBID and replace all the 3 short-forms and name this column as lookup

I will call the GeoOperation AddressPointLookup to geocode the lookup column to the respective latitude longitude coordinate as return as HDBPoint.  (For more information on the operation AddressPointLookup do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector...)

With this I will have a table with the HDBID as the linking key back to the HDB Resale Transaction and the HDBPoint which stores the latitude longitude coordinate of the HDB block.  This is a sample of the table:

As there are many HDB Blocks, showing them on a map is not easy to visualize

Let me use the GeoOperation Binning to group them into bins.  I will pass in the HDBID as the key field, HDBPoint as the point field from the HDB_LatLong table , set the type to be hexagonal, grid size to be 0.01 which is equivalent to 100m and the gridWithHeightRation to be 1.  (For more information on the operation Binning do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector...)

This is how it would look like

And I can color code the Bin, for e.g., Avg Resale Price so that it is easier to visualize as compared to the latitude longitude coordinate.

When I zoomed down to a few bins, the map will look weird as the bin covers the whole area:

Another way is to use the layer display to determine which layer to show under the zoom level:

In this map I have put in 3 layers (town, bin and point) determined by the zoom:

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area