Transposing Data

ckosty

Forums

Hi Brian -
I'm attempting to create a script to transpose a portion of the data that I have (example below).  I have been working with the Audimation HelpDesk directly but am still having some issues - figured I'd try here.  I've attached my IDEAScript code for reference. 
 
At the direction of the HelpDesk, I've populated an array with my "Grant Names" and am now attempting to append a new field to obtain the dollar amount of each "Grant Names" using a For Loop.  Once I get past this step, I will be able to Summarize on each individual's name and total each "Amount" column to effectively transpose.  I'm hung up on the 'append a new field' For Loop.  I keep getting a syntax error at "task.AppendField field".  If I comment out that line, my For Loop runs and populates the MsgBox with each new Field Name, so I feel as if the name is appropriate but perhaps not being accepted as a column header for some reason?  I'm stuck.  Let me know if you have any ideas.  The random MsgBoxs throughout were used to troubleshoot to make sure my array was populating appropriately, so those can be ignored.
 
The example below is what I am trying to accomplish.  Thanks for the help!
My data set is currently as follows upon importation:
 Name           Grant Name          Amount
John                 Grant 123               1,000
John                 Grant ABC             4,000
Jane                 Grant ABC              4,000
Jane                 Grant XYZ              2,000
Jane                 Grant 456                1,000
Alex                 Grant 456                 3,000 
 
The ideal output would be:
 Name              Grant 123          Grant 456          Grant ABC          Grant XYZ
 John                       1,000                          0                          4,000                                0
Jane                                 0                     1,000                      4,000                        2,000
Alex                                  0                     3,000                              0                                 0 

Brian Element Wed, 07/17/2019 - 15:10

Hi ckosty,

Depending on the size of the file wouldn't it be easier to create a pivot table and then save it as an IDEA table.

I imported your table:

I then created a pivot table out of the info:

I then saved it as an IDEA file which looks much like what you are looking for.  There are limitations on the size of the pivot table so if your file is too large this might work.

Let me know if this is an easier alternative for you or if you need help to create the code.

Brian

ckosty Thu, 07/18/2019 - 09:12

Thank you, Brian.  This is a much better option, truthfully it just never occurred to me that I was in essence just attempting to create a pivot table.  I was able to incorporate the Pivot Table into my macro and keep moving along, all worked perfectly.   Thanks again for your help.  (For the record, I was able to get the other way working too before I saw your response - so it was a double success!)

Brian Element Thu, 07/18/2019 - 09:25

No problem ckosty, glad I could help.  Yes, I knew you could do it the way you were going but usually if IDEA can do it internally I will let IDEA do it, in this case the pivot table works out great.

Good luck in your project.

Brian