cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GarryFarrell Regular Visitor
Regular Visitor

Unpivot issue

 

Hi,

 

I have files that have different data on the rows. See original file format below. I have multiple files loaded in my PBX file so far. I have created a Category column which I want to use as the column headings. My issues is that when I unpivot I never get whay I need. I required the format on the right 'Required Format'.

 

 

Capture.PNG

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Capstone Member
Member

Re: Unpivot issue

In the Query editor select the Category column, then under the Transform->Any Column sub menu click Pivot Column.

 

In the dialog box that appears change the Values Column to "Column1" from the drop down box

 

Click on advanced options and choose "don't aggrgate" from the Aggregate Value Function drop down box.

 

You should get the desired output.

 

Abhi

View solution in original post

Highlighted
Capstone Member
Member

Re: Unpivot issue

Hi,

 

I think I have solved your problem. Made a video, let me know if it works,

 

http://www.filedropper.com/groupfrommultipletextfiles

 

 

View solution in original post

7 REPLIES 7
Capstone Member
Member

Re: Unpivot issue

In the Query editor select the Category column, then under the Transform->Any Column sub menu click Pivot Column.

 

In the dialog box that appears change the Values Column to "Column1" from the drop down box

 

Click on advanced options and choose "don't aggrgate" from the Aggregate Value Function drop down box.

 

You should get the desired output.

 

Abhi

View solution in original post

GarryFarrell Regular Visitor
Regular Visitor

Re: Unpivot issue

Thank you.

 

I was using the right click pop-up menu and I could not find the pivot functionality which I needed. However, you answer showed me that it is on the Transform menu and not in the pop-up menu.

Capstone Member
Member

Re: Unpivot issue

Have edited my post to include the location of the Pivot Column option. Cheers.

GarryFarrell Regular Visitor
Regular Visitor

Re: Unpivot issue

 

 

 

GarryFarrell Regular Visitor
Regular Visitor

Re: Unpivot issue

 

I got the pivot to work. However when there are two sets of data in a file then I get an error "There were too many elements in the enumeration to complete the operation". This happens because there are now duplicates in the Source.Name and the Category columns. I only get the error for file1. I have tried to add an index column which removes the error but the data is then on different rows. I was thinking if instead of an index column maybe a row number for each file might help.

 

Capture1.PNG

Highlighted
Capstone Member
Member

Re: Unpivot issue

Hi,

 

I think I have solved your problem. Made a video, let me know if it works,

 

http://www.filedropper.com/groupfrommultipletextfiles

 

 

View solution in original post

GarryFarrell Regular Visitor
Regular Visitor

Re: Unpivot issue

Wow. Nice one. Thanks you saved me. Perfect.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 273 members 3,247 guests