Challenge #109: Women in Government Around the World

[DriveMyData]

Spoiler

[mceleavey]

I enjoyed this one. Kudos!

Spoiler

I started by applying filters to each input to remove those records that had not reporting in 1997 and 2017, and those that did not have valid income and region fields then joined together on the company code and transposed the year column values into a single column:

I then cleaned up the data using the auto field and data cleansing tools, and fixed the value field to apply the concept of taking the previous value in the case of a null. I then calculated the change difference between years:

I then split the stream into three to apply the additional restrictions, isolating the records that needed to be removed and driving the filter on the join:


I then sorted by Income Group and then descending on the 20YearChange field. A sample tool was then used to take the first three for each group:

[SeanAdams]

Solution below

Spoiler

[jezknee]

Solution attached.

[Carlithian]

Finally getting my head around these multi row formulas :) fun challenge 

[danilang]

Good challenge

I finally grasped the concept of splitting the stream, calculating a summary and then joining back to the original stream to filter out records!  Only took me ~80 weekly challenges.  But remember, never...cross...the...streams

Spoiler

Dan

[Aster]

Spoiler

Took me 40 minutes as I was not sure how to deal with countries that start reporting after 1997.

Wonders if there is a simpler way.

[AndyBate]

complete

[DavidP]

Great challenge! I've learnt how to use grouping in the Sample Tool, which is very handy!