Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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

MartinLuus
7 - Meteor
 
Vidya26
8 - Asteroid

My Solution!!!

RobertoEstrada
8 - Asteroid

Here is my solution.

GlyndwrCo
6 - Meteoroid
Spoiler
063.png
anaibeth
8 - Asteroid

Here's my weird AF solution. THis one really got me thinking

challenge 63.PNG

IvyM
7 - Meteor

Not very efficient, but I think it works. 

IvyM
7 - Meteor

Still trying to learn Multi-Row Formula tool. It is hard for me to write long expressions.

IvyM
7 - Meteor
 
RWvanLeeuwen
11 - Bolide

My solution works if this data file does not exceed 99 weeks of data and when the same three measures are being used repetitively. Of note: the proposed solution contains totals with a value of 0 so I..

Spoiler
ensured that it when the inside sales is null it is replaced to a 0 (same for outside sales) and then I ensured the total is the sum of the other two fields and it calculates that only when the total is null
63.png
asteryx
8 - Asteroid

Here's another not-so-pretty solution. Similar to many, but not as clean as others.

I learned about the Select Records tool and some options with the Dynamic Rename and, of course, got a lot more familiar with Transpose and Cross Tab.

Interesting note: when I added additional data columns to the source, my workflow failed initially. Turned out that each time I added a new column the 'DMA' column got pushed back by one column and was dynamically renamed to a different increment each time a new column was added. I had the Select tool with the Rename to "DMA" after the Dynamic Rename, so the rest of the workflow was always missing that expected field. I put the Rename earlier in the workflow and now it works for new columns each time.

 

Spoiler
Challenge 63.PNG