5 Replies Latest reply on Feb 14, 2013 10:20 AM by SteveWu

    Data transformation



      Data transformation


           I am new to FMP and I need to reassign data points from one master table to two separate tables. The master table looks like this --

           ID      Value1      Value2      Week

           101      21             51              1

           102      22             50              1     

           103      21             52              1     

           101      20             49              2

           102      19             53              2

           101      23             48              3


           I would like to reassign the numbers to two tables like these --

           Vaule1 table:

           ID        Week_1        Week_2        Week_3    

           101        21                  20                 23

           102        22                  19

           103        21


           Value2 table:

           ID        Week_1        Week_2        Week_3    

           101        51                  49                 48

           102        50                  53

           103        48


           I will appreciate any generous input on this matter. Thanks in advance!



        • 1. Re: Data transformation

               You don't actually need to define additional tables to get your data to display in those two formats. You can do this by adding additional layouts plus a 'self join' relationship that re-arrange your data into this format.

               Go to manage | Database | Relationships.

               Select the table occurrence box for your existing table and click the duplicate button (two green plus signs). This creates a new occurrence of your existing table, it does not duplicate the actual table. (Click over onto the Tables tab and see what is no shown on that tab.)

               Create this relationship:

               YourTable::ID = YourTable 2::ID 

               I'll use YourTable 2 as the name of the second occurrence, but you can actually rename this occurrence to be something more descriptive if you want.

               Now create a new layout based on YourTable. While in layout mode, double click the body layout part's label to go to part setup and change the body into a sub summary layout part "when sorted by ID". It won't matter whether you choose "print above" or "print below" for this part.

               Put the ID field in the sub summary layout part for column 1. Add a one row portal to YourTable 2 for Column 2. Give this portal a portal filter expression: YourTable 2::Week = 1. Put the Value 1 field inside the portal row. You can make the portal boundaries invisible if you give the borders 0 width or transparent line settings. Now make copies of this portal to add columns for additional weeks--changing the portal filter expression to specify different weeks.

               When you return to browse mode, be sure to sort your records by ID or your layout will appear blank.

               Now use Duplicate Layout to make a copy of this layout and chanage out the Value 1 fields from YourTable 2 with the value 2 fields from yourTable 2.

               Note: What I have described locks you into specific weeks in each column. If you add  a global field for specfiying the week for the first column, you can modify the portal filter expressions to show data for gWeekField + 1, gWeekField + 2, etc. You'll need to use Refresh Window [Flush Cached Join results] to update your report each time you change the value of the global gWeekfield.

          • 2. Re: Data transformation

                 Thank you, PhilModJunk! It works.

                 Now I can see the form view but nothing show up in the list view that I want to see. Did I miss something? Thanks again!

            • 3. Re: Data transformation

                   Are your records sorted correctly?

                   Unsorted records or sorted records that did not sort on the "sorted by" field specified in the sub summary layout part will produce a blank layout.

              • 4. Re: Data transformation

                     The records are sorted by "ID_long" that is specified in the sub summary layout part.

                     Here is the screen shot -- form view

                • 5. Re: Data transformation

                       here is the list view