Challenge #63: Data Preperation Using Pivot Tools

[Vidya26]

My Solution!!!

[RobertoEstrada]

Here is my solution.

[GlyndwrCo]

Spoiler

[anaibeth]

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

[IvyM]

Not very efficient, but I think it works. 

[IvyM]

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

[RWvanLeeuwen]

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

[asteryx]

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