IBM Apptio

 View Only

 Separate single column data into multiple columns

Jump to  Best Answer
  • CostingStandard(CT-Foundation)
Apptio Community Member's profile image
Apptio Community Member posted Tue June 29, 2021 09:51 AM
Does anyone know how to separate the data in a single column into multiple columns based on the actual data contained in the details?  My time tracking data is provided with one column containing each category.  Each person has multiple rows based on the category logged in their time tracking details.  

I would like to have the data in each Category type moved to a separate column, so that I would have multiple columns instead of one. 

For example, if an individual submits time for the week end of 7/2, in it's current state, it appears:

Employee Name/Week ending/Category/Time Worked
Row 1:  Joe Smith/7.2.2021/Admin/10
Row 2:  Joe Smith/7.2.2021/Project/10
Row 3:  Joe Smith/7.2.2021/Out of Office/20

Future state, it should appear: 
Employee Name/Week ending/Admin Time Worked/Project Time Worked/Out of Office Time Worked 
Row 1:  Joe Smith/7.2.2021/10/10/20


Thank you in advance for your assistance.  
Ashlea
#CostingStandard(CT-Foundation)
Jenny Franklin's profile image
Jenny Franklin posted Tue June 29, 2021 10:00 AM  Best Answer
Apptio Community Member updated Tue June 29, 2021 10:07 AM
@Ashlea Jensen, should be able to do something like the following:

Let's assume all your data is currently in one column called 'ColumnA'...  

Employee Name =Trim(Split(ColumnA,1,"/"))
Week ending =Trim(Split(ColumnA,2,"/"))
Category =Trim(Split(ColumnA,3"/"))
Time Worked =Trim(Split(ColumnA,4,"/"))

Be sure and make the Time Worked column numeric so you can sum later..
#CostingStandard(CT-Foundation)
Apptio Community Member's profile image
Apptio Community Member posted Tue June 29, 2021 10:31 AM
@Jenny Franklin - thank you for the options.  
Right now my data already has a separate column for each of the following (and more):
Employee Name
Week ending
Category
Time Worked

But, I need the the columns to have:
Employee Name
Week ending
Admin Time Worked
Project Time Worked
OOO Time Worked

So I can report on individuals as well as category types.  Does that make sense?  I might not be explaining it correctly, but I'm trying to keep all the data intact and expand the current Category column to breakout the categories into separate columns, instead of all in one. ​
#CostingStandard(CT-Foundation)
Jenny Franklin's profile image
Jenny Franklin posted Tue June 29, 2021 03:58 PM
So sorry @Ashlea Jensen - you'd think by now I would have learned to not respond to posts a) in the morning and b) before coffee.  It never works out well.  I mean, it's like I didn't even read your question.  Sheesh.  Sorry about that. 

So to get those hours in separate columns, here's what you could do:

Add the following formulas in your Formula step (all numeric):

Admin Time Worked =If(Search("admin",Category)>0,TimeWorked,"")

Project Time Worked =If(Search("project",Category)>0,TimeWorked,"")

Out of Office Time Worked =If(Search("office",Category)>0,TimeWorked,"")

​(BTW, I use the Search function instead of the Find function only because Search isn't case-sensitive.)

Also, you might want to make your TimeWorked column numeric (looks like a label since it's left-justified).
#CostingStandard(CT-Foundation)
Apptio Community Member's profile image
Apptio Community Member posted Wed June 30, 2021 08:52 AM
@Jenny Franklin - Thanks so much!  ​​
#CostingStandard(CT-Foundation)
Jenny Franklin's profile image
Jenny Franklin posted Wed June 30, 2021 09:11 AM
@Ashlea Jensen Happy to help! 😊​
#CostingStandard(CT-Foundation)

Related Content