Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #1: Join to Range

NishYou
5 - Atom

Hello! New player here :)

This is a great way to learn.

Thinking of tackling them all... slowly.

Shwetha1
5 - Atom
 
Shwetha1
5 - Atom

Using Find and replace tool...

ai_zahran
5 - Atom

My first challenge!

jnans
8 - Asteroid

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

dpcurtis
7 - Meteor

One down. Already learning.

Mausam
8 - Asteroid

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.1.JPG

This would create the Lower column in the source data with the correct lower values of the matching postal ranges.

2.JPG

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

3.JPG

Next join this output with the output from the previous step and use a summarize tool to get the desired result.

 

cwatters
7 - Meteor
 
RobertOdera
13 - Pulsar

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

KARR_Matthieu
5 - Atom

My first Challenge!