We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

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 #63: Data Preperation Using Pivot Tools

FilipR
11 - Bolide

Solved.

 

 

 

Yini_Liu
7 - Meteor

Nice Challenge.

sarahlovesdata_
6 - Meteoroid
Spoiler
Fun challenge!

 

NickRogers
8 - Asteroid

My attempt

Medha31
8 - Asteroid

Here is the solution

DanielG
12 - Quasar

Solved

Rob-Silk
8 - Asteroid

My solution below:

 

Spoiler
challenge_63_RS_screencap.PNG
SmitaLohande
8 - Asteroid

My Solution!!

BS_THE_ANALYST
13 - Pulsar

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

63 brute force lol.png

mceleavey
17 - Castor
17 - Castor

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

 

Spoiler
Screenshot 2024-01-16 160658.png

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.

 

 

 



Bulien