Apptio for All

Apptio for All

 View Only
Back to Library

Challenge 5: Break apart multiple values condensed within one table cell [+] 

Mon October 01, 2018 10:24 AM

Challenges are designed to help sharpen your TBM and Apptio skills. See title for rating: [+] Easy   [++] Moderate   [+++] Challenging

 

There are only TWO chances left to play TBM Pursuit in 2018! Challenges one, two, three, and four are already complete. Jump into the game if you haven't already. A correct answer is worth 30pts and a TBM Pursuit game piece 

 

Question Closed

@Chris Davidson says...

 

I'm going to start receiving monthly updates

to the storage and project data tables seen below:

 

 

 

 

So far, so good. But in my cost transparency project, I want to create object-to-object cost allocation strategies with a high level of granularity, ones which need to individually consider each Storage Device ID (in storage table) and Application (in project table).

 

Did you spot the problem with this approach?

Those two table columns (Storage Device ID and Application) have a few cells in which more than one value is included (separated by commas).

 

What I'm hoping for is some way (within Apptio, to help automate this monthly data transformation process) to break apart those cells to produce new additional table rows, in which all column values get repeated (except Storage Device ID and Application) for the affected cells.

 

Here are mockups of the results I'd like to see:

 

 

 

 

What modification(s) can I make to my two tables within Apptio in order to break the multiple-value cells into multiple rows instead?

 

 





#ApptioforAll

Statistics
0 Favorited
3 Views
0 Files
0 Shares
0 Downloads

Comments

Apptio Community Member
Thu April 25, 2019 01:24 PM

Oh @Tony Kolic, thank you for holding us accountable on this. @Meghan Johel was lined up to take the torch from @Chris Davidson this year but she ended up moving into an exciting role helping to evolve Insights sort of related to those in the Community "Cookbook."

 

There are plenty of experts to fill the spot, we just haven't kicked the program back into motion and It's been hanging over me to make it happen. I was thinking we'd jump in with a clean start on the second half of the year. When we do, I promise it will be be a big deal on the home page, so it's not a secret.  :-) 


#ApptioforAll

Apptio Community Member
Thu April 25, 2019 11:42 AM

When does the 2019 action start?


#ApptioforAll

Apptio Community Member
Mon December 03, 2018 09:45 AM

Bring it ON!!! I do rather enjoy these @Rhonda Keller, @Chris Davidson, & Megan Beasley


#ApptioforAll

Apptio Community Member
Fri November 30, 2018 01:39 PM

YES @Shannon Lynch  Final challenge of the year coming next week 


#ApptioforAll

Apptio Community Member
Thu November 29, 2018 01:39 PM

Hi @Chris Davidson & Megan Beasley,

 

Will there be another pursuit in 2018?


#ApptioforAll

Julie Batty
Mon November 05, 2018 03:55 AM

Well done everyone. Can't believe I've missed another community pursuit - darn work getting in the way


#ApptioforAll

Apptio Community Member
Thu November 01, 2018 12:14 PM

Awesome job by all!! You have now been awarded the 5th Community pursuit piece (err...badge!) to your profiles!  


#ApptioforAll

Chris Davidson
Thu November 01, 2018 10:23 AM

Congratulations to 26 of you who submitted a correct answer:

Andrés Santín
Suresh Sawlani
Jeetendra Suresh Kaushik
Tony Wong
Brady Panter
Jug Rusit
Steven Young
William Bunney
Jonathan Strauss
Shannon Lynch
Michelle McGuire
Paula Foster
Matt Erickson
Fabian Woldsen
Rene Rahn
Amy Liu
Samir Banker
Chris Wilson
Jeremy Morin
Jeetendra Kaushik
Axel Burkert
Tobias Zimmermann
Martyn Dawes
Kalyani Kumari Patra
Jenny Franklin
Susmitha Morisetti

 

In R12, SplitEx() function is the quickest option:

Application = SplitEx(Application,",") or Application = SplitEx({$_},",")

Storage Device ID = SplitEx(Storage Device ID,",") or Storage Device ID = SplitEx({$_},",")

 

In R11, Duplicate Rows Using Split option works well.

We could enter a comma in the Duplicate Rows Using Split field within the Application and Storage Device ID column details.

 

A few of you mentioned an alternative of using Split() function and then Collapse Grid By (R11) or Unpivot (R12).


#ApptioforAll