AnsweredAssumed Answered

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

Question asked by hugall1998 on Jul 13, 2017
Latest reply on Jul 13, 2017 by beverly

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.

Outcomes