Challenge #63: Data Preperation Using Pivot Tools

[FilipR]

Solved.

[Yini_Liu]

Nice Challenge.

[sarahlovesdata_]

Spoiler

Fun challenge!

[NickRogers]

My attempt

[Medha31]

Here is the solution

[DanielG]

Solved

[Rob-Silk]

My solution below:

Spoiler

[SmitaLohande]

My Solution!!

[BS_THE_ANALYST]

Fun stuff. Went brute force for method one. I'm liking the idea of pushing forward and not giving up and then refactoring afterwards. Two different approach. Love the Make Columns tool. Very quick for manipulation in cases like this

 

[mceleavey]

A nice example of the pivot tools (Crosstab and Transpose)

Spoiler

I first Transposed the data grouping by the first column and split the stream to isolate those where the first column was blank.  Here, I assigned the value in the "Value" column column to fill in the blanks and joined it back to the main stream on the Name field (matching the columns). From here I converted the date from the date field to only take the second date and convert it to a real date format.

I then used a multi-row to determine the header of the column from where the first column contained the header and copied it down to all parts of that group. I then simply removed the null values and cross-tabbed back using this header field as the header, the Value field and grouped by the Week Of and DMA fields.

The last thing was to simply replace the blanks with zero.

Nice.