This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!
Submit FeedbackI used a simple mathematical trick.
If you look at the postcode ranges in the reference table, the ranges are categorized into 20 postal areas starting from 2000. So, 2000-2019 has 20 postal areas for example. The only catch to this is that the last range 2080-2100 has 21 postal areas because it ends with 2100 instead of 2099.
So, with the formula tool on the source data, first I found the mod of the postal area for division by 20. So 2086 mod 20 would be 6. Now if you subtract 6 from 2086 you would get 2080. Using this trick you can get the matching lower range for all the postal areas. For example, 2051 would correctly give the lower range as 2040. However, as said before, 2100 should give the lower range as 2080 and not 2100. For this special case, we could use the 'if then else endif' statement.
This would create the Lower column in the source data with the correct lower values of the matching postal ranges.
Now split the reference table postal ranges, using the 'text to columns' tool with delimiter '-'. Use the select tool to call the first column 'Lower'.
Next join this output with the output from the previous step and use a summarize tool to get the desired result.