AnsweredAssumed Answered

Transposing Data on Import to FileMaker

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

Title

Transposing Data on Import to FileMaker

Post

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?

Outcomes