Challenge #1: Join to Range

[NishYou]

Hello! New player here :)

This is a great way to learn.

Thinking of tackling them all... slowly.

[Shwetha1]

Using Find and replace tool...

[ai_zahran]

My first challenge!

[jnans]

Attached is my solution to this challenge. I am looking forward to see how others solved this.

[dpcurtis]

One down. Already learning.

[Mausam]

I 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.

[RobertOdera]

Took the Formula tool + Find & Replace tool route...

[KARR_Matthieu]

My first Challenge!