1 2 Previous Next 15 Replies Latest reply on Jun 26, 2012 3:23 PM by philmodjunk

    recoding data from one variable into another new variable

    KevinSiddons

      Title

      recoding data from one variable into another new variable

      Post

      I'm new to FileMaker and could use some help getting oriented.

      I'm doing analysis of 5 years of health data which I would like to store in FileMaker. I have files of original data which I've imported into FileMaker. In preparation of my statistical analysis (in SPSS) I want to recode data from one variable into another newly named variable.

      I'm working with yearly Cause of Death data. Each record represents an individual who died. Each record has a coded COD. I need to create a new variable that would represent a range of COD codes (for example - Heart disease would be a category that would contain a number of COD codes). I would like to run a 'script' that would complete this coding for all records.

      I've programmed in SQL and am familiar with structured programming. I haven't yet gotten a sense on how to do this in FileMaker.

      Thanks in advance for your help.

      Kevin

        • 1. Re: recoding data from one variable into another new variable
          philmodjunk

          First, in FileMaker, there are fields and there are variables, they aren't the same thing. It sounds like you want to move data into a field rather than a variable but I could easily be wrong.

          It sounds like you want to build a list such as Myocardial Infarction, Congestive Heart Failure, etc for your list of COD's for "Heart Failure".

          It's hard to say for sure without having a clearer understanding of what you want to do with such a list, but it sounds like you should be creating records in a related table where you have one field for COD and one field each category such as "Heart Disease". You'd have one record for each COD and those that fall in the same category would have a common value in the "category" field.

          It's even possible that you may need a single COD to be a member of more than one Category. That can be handled in a relational database also.

          Note: while you will find that FileMaker doesn't don things like you would expect if you could use SQL, the  underlying basic structure of tables and relationships is not that different from any other Relational Database system.

          • 2. Re: recoding data from one variable into another new variable
            KevinSiddons

            All righ, let me give some more information.

            The records already have a field/variable that contains the COD data. Each record has one. I want to add a field/variable that would be called something like COD_category.

            I want to run a script that will check the COD value and place a value in COD_category.

            This will all take place in a single table. In that way it is flat table. I just want to add a new variable/field that will have a value placed in it based upon another field/variable in the table.

            I hope this helps.

             

            Kevin

            • 3. Re: recoding data from one variable into another new variable
              philmodjunk

              Ok, take "variable" out of your request here. It means something specific in FileMaker and you want a field.

              How will your script know what category to assign to a given COD?

              Option 1:

              I suggest you set up that related table manually so that you have records for each COD and a Category field in the record can identify the category. One record for each COD. You can set up this table by importing records from your current file, but set up a "Unique values, validate always" validation rule on the COD field in this new table. That filters out all the duplicate COD values. You'd then update the Category field to assign each COD to a specific category.

              Then you can define this relationship:

              OriginalTable::COD = CODCategories::COD

              You can simply display the CODcategories::Category field on a layout based on your original table or you can use Replace Field contents with the calculation option to copy the value from CODcategories::Category into a category field in your original table.

              Option 2:

              If you only intend to assign relative few of all possible COD's to a category (maybe only certain COD's are of interest to you), You can perform a find for a given COD, enter or (better) select the category from a value list for one record, then use Replace field contents to update the other records with the same COD to have the same Category.

              Option 3:

              You might adapt Option 2 to use a looping script that finds all records with a given COD and pauses, you enter/select a category and continue the script, it updates the remaining records with the same COD and then finds a new group of records with the same COD but no assigned category...

              • 4. Re: recoding data from one variable into another new variable
                KevinSiddons

                Here's an example of as SQL code I used to accomplish this task:

                Convert COD codes to WHO ICD-10 categories

                UPDATE Deaths_2006
                SET COD_category = 'Certain Infectious & Parasitic Diseases'
                WHERE Cause_of_Death_code in ('A047','A048','A084','A410','A419','B182','B201','B203', 'B227','B91','B942')

                UPDATE Deaths_2006
                SET COD_category = 'Neoplasms'
                WHERE Cause_of_Death_code in ('C029','C119','C159','C160','C169','C189','C20','C220',
                                                                             'C221','C229','C23','C259','C349','C411','C412','C419',
                                                                             'C435','C439','C411','C412','C419','C435','C439','C444',
                                                                             'C459','C509','C539','C541','C55','C56','C570','C61','C64',
                                                                             'C679','C719','C720','C73','C760','C787','C793','C80','C851',
                                                                             'C859','C900','C910','C915','C920','C97','D329','D371','D377',
                                                                             'D432','D469')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Blood & Blood-Forming Organs & Certain Disorders Involving the Immune Mechanishm'
                WHERE Cause_of_Death_code in ('D500','D589','D649','D689','D70','D869')

                UPDATE Deaths_2006
                SET COD_category = 'Endrocrine, Nutritional & Metabolic Disease'
                WHERE Cause_of_Death_code in ('E039','E105','E109','E117','E119','E141','E145','E147','E149','E662','E668','E785','E870')

                UPDATE Deaths_2006
                SET COD_category = 'Mental & Behavioral Disorders'
                WHERE Cause_of_Death_code in ('F019','F03','F051','F107','F171','F209')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Nervous System'
                WHERE Cause_of_Death_code in ('G061','G122','G20','G219','G301','G309','G312','G319','G35','G700','G711','G912')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Circulatory System'
                WHERE Cause_of_Death_code in ('I080','I10','I110','I119','I120','I219','I248','I250','I251','I255','I258','I269',
                                                                             'I272','I279','I313','I340','I350','I359','I38','I429','I442','I461','I469','I48',
                                                                             'I490','I499','I500','I509','I516','I519','I609','I619','I634','I639','I64','I679',
                                                                             'I694','I709','I710','I713','I739','I772','I776','I859')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Respiratory System'
                WHERE Cause_of_Death_code in ('J152','J180','J182','J189','J439','J448','J449','J459','J61','J690','J80','J841',
                                                                    'J848','J849','J90','J948','J969','J988')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Digestive System'
                WHERE Cause_of_Death_code in ('K449','K529','K550','K559','K566','K578','K631','K659',
                                                                    'K729','K746','K767','K769','K804','K922')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Skin & Subcutaneous Tissue'
                WHERE Cause_of_Death_code = 'L031'

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Musuloskeletal System & Connective Tissue'
                WHERE Cause_of_Death_code in ('M069','M321','M331','M349','M402','M793','M819','M879')

                UPDATE Deaths_2006
                SET COD_category = 'Diseases of the Genitourinary System'
                WHERE Cause_of_Death_code in ('N039','N170','N179','N180','N189','N19','N390')

                UPDATE Deaths_2006
                SET COD_category = 'Certain Conditions Originating in the Perinatal Period'
                WHERE Cause_of_Death_code in ('P011','P021','P072','P073','P209','P369','P392')

                UPDATE Deaths_2006
                SET COD_category = 'Congenital Malformations, Deformations & Chromosomal Abnormalities'
                WHERE Cause_of_Death_code in ('Q019','Q249','Q913')

                UPDATE Deaths_2006
                SET COD_category = 'Symptoms, signs and abnormal clinical and laboratory findings, not elsewhere classified'
                WHERE Cause_of_Death_code in ('R048','R092','R54','R570','R628','R99')

                UPDATE Deaths_2006
                SET COD_category = 'External Causes of Morbidity & Mortality'
                WHERE Cause_of_Death_code in ('V092','V279','V294','V499','V877','V892',
                                                                              'W06','W08','W10','W19','W74','W80',
                                                                              'X42','X44','X59','X67','X70','X85','X95','X99',
                                                                              'Y08','Y14','Y835','Y850','Y86','Y872')

                I would like to accomplish this in FileMaker.
                 
                Thanks in advance.
                 
                Kevin

                • 5. Re: recoding data from one variable into another new variable
                  Sorbsbuster

                  As Phil suggested in Option 1:

                  - Set up a second table with two fields; CODCode and CODCategory
                  - the records will look like

                  CODCode     CODCategory
                  V092           External Causes of Morbidity & Mortality
                  V279           External Causes of Morbidity & Mortality
                  V294           External Causes of Morbidity & Mortality
                  ...etc
                  N039           Diseases of the Genitourinary System
                  N170           Diseases of the Genitourinary System
                  ...etc...

                  Then in the first table set the CODCategory to be Auto-enter by lookup.

                  • 6. Re: recoding data from one variable into another new variable
                    KevinSiddons

                    O.k. I am familiar with Lookup tables.

                    I guess I was trying to see of FileMaker handled data recoding like an SQL database. Does FileMaker have a scripting language that works in a similar manner (to SQL)?

                     

                    Thanks for responding (btw)

                     

                    Kevin

                    • 7. Re: recoding data from one variable into another new variable
                      philmodjunk

                      Don't get us wrong here, you CAN do what you describe with a script, it's just that if you are going to use a realtional database, you should consider taking advantage of the features a relational data base offers to make a task easier. Either way, you have to type in the list of COD's and their categories. By using a related table, you can input the data faster, with fewer chances of error and you can easily correct errors after the fact without having to run your script all over again. The same advantage holds if the powers that be add a new COD or change categories. With a related table, this change becomes a data entry task instead of a much more complex programming task.

                      Here's a sample script if you still want to do it that way: (to save time, didn't put all the codes in this string.)

                      If [Patterncount ( "I080I10I110I119I120I219I248I250I251I255I258I269I272I279I313I340I350I359I38I429I442I461I469I48" ; CODField ) ]
                          Set Field [YourTable::COD_Category ; "Diseases of the Circulatory System"]
                      Else IF [PatternCount ("next string of codes here...

                      You could also set up a single set field step that uses a case statement to use the pattern count function to check for "set membership" in order to assign a Category.

                      Please note that even with an SQL based database, I wouldn't perform this type of task with just an SQL query, I'd still set up such a table for the reasons I have already listed in this post.


                      • 8. Re: recoding data from one variable into another new variable
                        KevinSiddons

                        Thanks! I'll experiment with both methods and report back.

                         

                        Thanks again.

                         

                        Kevin

                        • 9. Re: recoding data from one variable into another new variable
                          KevinSiddons

                          Alright, I'm building look up tables (that will go on for a while).

                          Here's another question:

                          I have a SQL script that recodes a date (formated mmddyyyy) to an ISODATE (formated yyyymmdd). Here's the code:

                          UPDATE Deaths_2006

                          SET DOD = substr(DOD_raw,5,4) || substr(DOD_raw,1,2) || substr(DOD_raw,3,2)

                          WHERE DOD_raw isnot null

                          How would this look in FileMaker?

                           

                          Thanks in advance.

                          Kevin

                          • 10. Re: recoding data from one variable into another new variable
                            philmodjunk

                            The WHERE part is done by performing a find on your layout--either by hand or in a script where you specify a lone * in the DOD_raw field to find all records where this field is not empty.

                            Then, in a script or by hand, you can use replace field contents with this calculation:

                            Replace Field Contents [no dialog; YourTable::DOD ; date ( Middle ( DOD_Raw ; 1 ; 2 ) ; Middle ( DOD_Raw ; 3 ; 2 ) ; Middle ( DOD_Raw ; 5 ; 4 ) ) ]

                            This assumes that DOD is a field of type date, that DOD_Raw is text where the date digits are in MMDDYYYY format. If you have DDMMYYYY format, you'll need to swap the first and second calls to the MIddle function.

                            Note: most menu actions for working with your data have matching scripts steps so you can often figure out what you want to do by doing the steps by hand and then you can code it into a script in order to automate it.

                            The fact that any casual user of your database can, (Unless you design to prevent it), manipulate the found set (roughly similar to a record set in other systems ) by finding records and/or sorting them simply by making menu choices can take some getting used to for many programmers more used to systems where a SQL query has to be defined and performed before the record set for a given "form" (layout in FileMaker terms) may be changed.

                            • 11. Re: recoding data from one variable into another new variable
                              KevinSiddons

                              Great!

                              Another question - in SQL I would use the UNIQUE command in order to create a list of the COD codes that I had to recode. Would I use a report to create a list of unique COD codes? (my table has 700+ records; lots of repeated COD codes)

                              • 12. Re: recoding data from one variable into another new variable
                                philmodjunk

                                Working with distinct lists of values in FileMaker can seem rather bizarre to experienced SQL programmers.

                                It depends on what you want to do with the list of unique COD values.

                                If you refer back a few posts, I mentioned that you could import the data from your current table into the new table and use field validations on the COD field in the related table to enforce unique values. This filters out the duplicates during the import process--producing a table with one record for each unique COD value.

                                For a report, you can set up a summary report where you see each COD listed only once even though you might have hundreds of records with the same CID value.

                                1. Create a new blank layout and enter layout mode.
                                2. Double click the label for the body layout part to open the part setup.. dialog.
                                3. Change the body layout part to a sub summary part "when sorted by" the COD field.
                                4. Put your COD field in this layout part.

                                 

                                If you now return to browse mode, you'll see a blank layout. This is normal at this point as the sub summary part is only visible when the records are sorted to group them by COD.

                                If you then show all records, sort by the COD field and specify View as List as the view mode. You'll see a list of records with each unique value in COD listed only once. You can also perform finds to see such a list, but limited to a sub set of all the records in your table. The find would be performed via a script or by hand on a layout based on the same table occurrence where you perform the find there and then switch to this layout to see the list.

                                With scripting, you can pop up such a list in a floating window if that is desired.

                                On the other hand, you can set up a value list that specifies values from the COD field and each value will be listed once in ascending order in the value list. And there's a function: ValueListItems, that can retriever that list of values from this value list.

                                You can even use SQL to get your list if you are using Filemaker 12 and want to see all the values listed in a single scrolling text field: A new way to count unique values in FileMaker 12

                                • 13. Re: recoding data from one variable into another new variable
                                  Sorbsbuster

                                  "Does FileMaker have a scripting language that works in a similar manner (to SQL)?" - I hope not, and Heaven forfend that it should ever come to pass.

                                  • 14. Re: recoding data from one variable into another new variable
                                    KevinSiddons

                                    @PhilModJunk - I usually run a quick query to find out all the values I have to include in my lookup table.

                                    This database is strictly for my use; I do statistical analysis on the finished table with the decoded/recoded values. The list of unique COD code values is for me; quick and dirty is best.

                                    Thanks.

                                     

                                    Kevin

                                    1 2 Previous Next