1 Reply Latest reply on Jul 13, 2017 5:47 AM by beverly

    Transposing records to columns and save as csv file - Seek improvements

    hugall1998

      Hi all, I ran into the above mentioned problem and solved it with a script but have the nagging feeling the solution could be much simpler and faster. The enclosed file is a mock up of a much larger database I'm currently developing and before I apply the solution to the bigger project I would appreciate your comments on improvements.

       

      My system is Win 64 bit, FM 15.0.2.  The enclosed mock-up file has two related tables, ID related to attributes (events).  In this mock-up, each ID has attended (visited) different number of events (cities).

       

      THE PURPOSE:

      - To transpose ID's related records, each with an attribute (event)  to columns and save as csv file, with one ID pr row and each attribute in a separate column.

       

      THE CHALLENGE:

      -  The techniques to transpose FM data to export to a regular row/column file all seem to rely on a fixed number, of either records or fields. If the number of records pr ID to be transposed are unequal and/or there is different overlap of attributes (events), then the values pr attribute will not go to the correct attribute column.

          Example:   ID A has 3 records, with attritbutes x1, x3 and x4 and corresponding values. 

                              ID B has 4 records, with attributes x2, x3, x4 and x5 and corresponding values.

                              ID C has 2 records with attributes x1 and x4 and corresponding values.

      If this data is gathered with a global variable and virtual list tech, then the exported row/column file will be mixed up as the number of attributes pr ID is not equal.

       

      THE SOLUTION:

      - This solution presented here is probably low tech but it only uses one global field and one script with loops and variables, but it works. The script has 5 steps and what I seek are suggestions for simplification (and perhaps more elegance), before applying it to a larger project.

       

      THE OUTPUT:

      - See below, after running the script, all IDs have the same number of columns and for those attributes that have not been registered for the ID, the script returns "[null]" (can be any value the user decides). 

       

      I'll be thankful for any and all comments and suggested improvements. I'm a intermediate user and look forward to learn more advanced techniques.