AnsweredAssumed Answered

Transposing Data on Import to FileMaker

Question asked by MattTurner on Jun 21, 2011
Latest reply on Jun 21, 2011 by philmodjunk


Transposing Data on Import to FileMaker


I would like to import data that is currently stored in an Excel spreadsheet in the following format:

ID V1 V2 V3 V4
ABC123 7 1 10 4
ABC124 8 2 11 5
ABC125 9 3 12 6

Where each entry has a unique value in the ID field, V1, V2, V3... are different variables.

One of the problems that we have is that in some instances an entry may have a different set of Variables (say, V5, V6, and V7, or only V1 and V3).  In order to convert these data into a relational database, I would like to store them in FileMaker in the following way:

ID Variable Value
ABC123 V1 7
ABC123 V2 1
ABC123 V3 10
ABC123 V4 4
ABC124 V1 8
ABC124 V2 2
ABC124 V3 11
ABC124 V4 5

This really isn't a "transpose" operation as in Excel.  Rather, it is creating a unique entry for each combination of ID and Variable. Does FileMaker have a simple way to do this operation?