Challenge #63: Data Preperation Using Pivot Tools

The solution to last week's challenge can be found HERE.

If you have even been shy about getting to know the crosstab/pivot tools, this challenge will serve as an excellent guide to becoming better acquainted with the tools.

An organization receives an input file like this every month or so. Additional columns are added to the file each time, representing data for more weeks. They need a workflow that will pivot the data so that each week end date is included on a row for each DMA number in column 1. The workflow needs to allow for the fact that additional columns will be included in later months.

challenge_63_start_file.yxmd

challenge_63_solution.yxmd

Developer

Preparation

Advanced

Data Analysis

Intermediate

Accepted answers

All comments

jamesrichards

After looking at the official solution, I feel good about mine. While working on it, it seemed so convoluted I thought there must be a better answer!

challenge_63_finished.yxmd

ipeng

It took longer than I expected

IP_challenge_63_start_file.yxmd

RolandSchubert

My solution

challenge_63_solution_rsc.yxmd

KeithTroutt

My solution. I used a few more tools than the solution provided but I achieved the same results.

Spoiler

challenge_63_solution_keithtroutt.yxmd

RichoBsJ

Hi! Here my solution

challenge_63_richobsj.yxmd

Mikkel_D_Karlsen

Thank you for a good challenge, it deserves a star

Here is my solution with the use of Basic tools

Spoiler

Test 63 V2.yxmd

yash123

Would the difficulty in the designer core exam be the same as this challenge?

Karam

My solution ..

Spoiler

challenge_63_start_file_Karam.yxmd

sachin02

My solution:

challenge_63_start_sachin.yxmd

cplewis90

A lengthy solution to work through this one. I definitely look forward to seeing other solutions to see if there is any simpler solutions.

Spoiler

challenge_63_CL.yxmd

ahmadtookhy

Check out my solution.

I am pretty confident no one did it in that way before me

challenge_63_Toukhy_Solution.yxmd

hanykowska

Mine is not a pretty or concise solution but seems to work...

challenge_63_solution_hanykowska.yxmd

JonathanAllenby

Here's my solution to challenge 63.

JonAllenby_Challenge63_Solution.yxmd

KMiller

Solution attached.

Spoiler

KM_challenge_63_solution.yxmd

pedrodebs

My solution.

challenge_63_PD solution.yxmd

StefanWe

Attached my Solution. I added a formula to calculate the total point of sales if missing. Additional Idea: could be extended to validate the data.

challenge_63_Solution_Stefan.yxmd

Adara

my solution, biggest annoyance was renaming the column headers. will look for a better solution next time

Spoiler

challenge_63_start_file sarahs answer.yxmd

TonyA

Probably the hardest challenge I've done yet. I think I have a mental block about the pivot tools. First time I had to ask for help. Anyway, here's my solution.