Reply
Highlighted
White Belt
Posts: 8
Registered: ‎02-15-2017
Accepted Solution

Collapse/Uncollapse Columns

At this point I'm very comfortable with every transformation except the two in the headline. I'm trying to determine which I should use and how to use it to breakout data from 1 column into multiple columns. 

 

I have one column called "Ad Type" where there are 5 different ad types displayed. Then I have viewable and standard impressions as a field.

 

I'd like to have the impressions for each ad type displayed in a separate column so ad type 1 would have 2 columns, one for viewable and one for standard impressions, ad type 2 would have the same thing and so on.

 

How would you approach this issue? Also ad type is not the only level of granularity within the data set (it's a placement, category, device and ad type level report. Assistance would be very helpful.


Accepted Solutions
Solution
Accepted by topic author D-Rab
‎08-10-2017 12:46 PM
Green Belt
Posts: 65
Registered: ‎03-17-2017

Re: Collapse/Uncollapse Columns

Collapse and Uncollapse Columns... Sometimes you will hear this as pivot and unpivot data.  I will post some example pics here to show you how I use these transformations.  I have a data set that lists goals by month.  However, each month is listed as a separate column and I wanted one column for Month and a seperate column for goal...1.pngI use the Collaps Columns transformation to accomplish this. You first specify what you want the New column that is to be made up of the current column labels to be called (in my case I chose "Month") and what you want the column that will hold the values to be called (in my case I chose "Goal").  You then select what columns are going to be collapsed.  You can also change the label at this point if you choose to...2.png

 

 That will give you the data "unpivoted"...

3.png

 

 

 

Now, if you want to reverse the process, you would need to "Uncollapse Columns".  For this tranformation, you need to select the column that is made up of what you want to turn into column labels ("Month" in my example).  Then the columns that identify a row (unique identifier if you have one, or you can just select all of the fields that you are not "uncollapsing").  Number 3 is the name of the new columns to create.  You need to have a different name for each column that you want to make ("m01, m02, m03, etc" for me).  In step 4 you tell them what label they will find in the column you selected in step one that identifies the row as belonging to each new column.  And in step 5 you tell the transformation what value should go in the new column...4.png

 

 

5.png

 

 

 

That will then give you the "pivoted" data, or the Uncollapsed view again...

6.png

 

 

 

Hope that helps to answer your question

 

 

 

 

 

 

 

 

View solution in original post


All Replies
Solution
Accepted by topic author D-Rab
‎08-10-2017 12:46 PM
Green Belt
Posts: 65
Registered: ‎03-17-2017

Re: Collapse/Uncollapse Columns

Collapse and Uncollapse Columns... Sometimes you will hear this as pivot and unpivot data.  I will post some example pics here to show you how I use these transformations.  I have a data set that lists goals by month.  However, each month is listed as a separate column and I wanted one column for Month and a seperate column for goal...1.pngI use the Collaps Columns transformation to accomplish this. You first specify what you want the New column that is to be made up of the current column labels to be called (in my case I chose "Month") and what you want the column that will hold the values to be called (in my case I chose "Goal").  You then select what columns are going to be collapsed.  You can also change the label at this point if you choose to...2.png

 

 That will give you the data "unpivoted"...

3.png

 

 

 

Now, if you want to reverse the process, you would need to "Uncollapse Columns".  For this tranformation, you need to select the column that is made up of what you want to turn into column labels ("Month" in my example).  Then the columns that identify a row (unique identifier if you have one, or you can just select all of the fields that you are not "uncollapsing").  Number 3 is the name of the new columns to create.  You need to have a different name for each column that you want to make ("m01, m02, m03, etc" for me).  In step 4 you tell them what label they will find in the column you selected in step one that identifies the row as belonging to each new column.  And in step 5 you tell the transformation what value should go in the new column...4.png

 

 

5.png

 

 

 

That will then give you the "pivoted" data, or the Uncollapsed view again...

6.png

 

 

 

Hope that helps to answer your question

 

 

 

 

 

 

 

 

Announcements
Domopalooza 2018 Call for Presenters! Do you have an amazing story about how Domo is revolutionizing the way you do business? Click here for more details! Thanks!