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
There would be different results if countries w/ nulls in 1997 have a history we're looking for and have been filtered out.
A nice data prep challenge! I ran into some pitfalls with the multi-row formula tool. Would have gotten it within 15 minutes otherwise.
Good one and quite relevant to some stuff I need to do for work. I beat my head against the wall for about 20 minutes trying to see why my answer didn't match yours, when I found I had filtered on "<2" instead of "<=2"....
Spoiler
The directions pretty well defined the process I followed:
- Transpose data to put the years into columns
- Filter out data not between 1997 and 2017
- Count instances where data is null
- Filter out those that provided no data (I think the directions were a bit unclear as to whether any missing data should be filtered out vs those that never provided data - I assume it meant include them if they ever provided data and exclude them if they never did (that is, 21 nulls)
- Set missing records equal to prior record (with grouping by country)
- Join with other input data to get region and income group
- Determine if percentage declined year over year and filter out countries those that declined more than twice
- Cross-tab to put data back into columns
- Calculate difference between 2017 and 1997 (or use 2017 for difference if 1997 is null)
- Sort descrneding
- Determine top 3 for each income group
- Transpose data to put the years into columns
- Filter out data not between 1997 and 2017
- Count instances where data is null
- Filter out those that provided no data (I think the directions were a bit unclear as to whether any missing data should be filtered out vs those that never provided data - I assume it meant include them if they ever provided data and exclude them if they never did (that is, 21 nulls)
- Set missing records equal to prior record (with grouping by country)
- Join with other input data to get region and income group
- Determine if percentage declined year over year and filter out countries those that declined more than twice
- Cross-tab to put data back into columns
- Calculate difference between 2017 and 1997 (or use 2017 for difference if 1997 is null)
- Sort descrneding
- Determine top 3 for each income group
