5 Replies Latest reply on Dec 13, 2013 12:43 PM by laura1553

    Case function vs look up table(s)

    laura1553

      Title

      Case function vs look up table(s)

      Post

           Hi there.  I am using some long Case function statements to calculate Standard test scores based on two conditions: test age and raw score.  The Case statements work, but I have bumped up against number of character limits and some unidentifiable limit (seems based on length of statement) that require me to edit the formulas for each test period.  I wondered if a look up table would work, but I don't know how I could structure that with the age ranges and raw scores. Any help or other ideas/solutions would be greatly appreciated!  :)

           Here's the beginning of one of the statements with a couple of notes:

           Let ( [ 
           f1 = Y2AP1_TestingDetail::Y2AP1_B1_Test_Age ;
           f2 = Y2AP1_WIATRC_W_RAW  ; 
           a4 = f1 ≤ 7.03 ; 
           a5 = f1 ≤ 7.07 ; 
           a6 = f1 ≤ 7.11 ; 
           a7 = f1 ≤ 8.03 ; 
           a8 = f1 ≤ 8.07 ; 
           a9 = f1 ≤ 8.11 ; 
           a10 = f1 ≤ 9.03 ; 
           a11 = f1 ≤ 9.07 ; 
           a12 = f1 ≤ 9.11 ; 
           a13 = f1 ≤ 10.03 ; 
           a14 = f1 ≤ 10.07  
           // WIATWR SS calc uses additional range of 10.08 - 10.11
           ] ;
            
           Case (
           f1 ≤ 6.11 ; "out of range" ; 
           f1 ≥ 10.08 ; "out of range" ; 
            
           a4 and f2 ≤ 0 ; 56 ; a4 and f2 ≤ 1 ; 60 ; a4 and f2 ≤ 2 ; 63 ; a4 and f2 ≤ 3 ; 65 ; a4 and f2 ≤ 4 ; 67 ; a4 and f2 ≤ 5 ; 69 ; a4 and f2 ≤ 6 ; 70 ; a4 and f2 ≤ 7 ; 71 ; a4 and f2 ≤ 9 ; 72 ; a4 and f2 ≤ 10 ; 73 ; a4 and f2 ≤ 11 ; 74 ; a4 and f2 ≤ 12 ; 75 ; a4 and f2 ≤ 14 ; 76 ; a4 and f2 ≤ 15 ; 77 ; a4 and f2 ≤ 16 ; 78 ; a4 and f2 ≤ 17 ; 79 ; a4 and f2 ≤ 19 ; 80 ; a4 and f2 ≤ 20 ; 81 ; a4 and f2 ≤ 21 ; 82 ;
           ETC -- tests for this age range continue for a while.
            
           a5 and f2 ≤ 0 ; 49 ; a5 and f2 ≤ 1 ; 53 ; a5 and f2 ≤ 2 ; 56 ; a5 and f2 ≤ 3 ; 58 ; a5 and f2 ≤ 4 ; 59 ; a5 and f2 ≤ 5 ; 60 ; a5 and f2 ≤ 6 ; 61 ; a5 and f2 ≤ 7 ; 62 ; a5 and f2 ≤ 8 ; 63 ; a5 and f2 ≤ 9 ; 64 ; a5 and f2 ≤ 10 ; 65 ; a5 and f2 ≤ 11 ; 66 ; a5 and f2 ≤ 12 ; 67 ; a5 and f2 ≤ 13 ; 68 ; a5 and f2 ≤ 14 ; 69 ; a5 and f2 ≤ 15 ; 70 ;
           ETC, ETC for each age range.

           Thank you!

        • 1. Re: Case function vs look up table(s)
          philmodjunk

               This looks somewhat familiar. Have you asked about this in the past?

               A look up table could be constructed for this and it would be much, much easier to manage and allows you to change the criteria by editing values in a table instead of redefining a case function calculation.

               If I understand the logic correctly here, you have different results to return for values of f1 and f2 that fall in specific ranges of values. such as

               f1 values of 6.12...7.03 that also have f2 values of < 0 should return a value of 56

               Same f1 range and f2 > 0 and f2 < 1 returns 60

               and so forth...

               If I have parsed this function correct, then you could set up a lookup table of values that looks like this for the first few entries:

               f1    |    f2    | Result
               7.03|     0    | 56
               7.03|     1    | 60
               7.03|     2    | 63

               and so forth for the first group...

               The next group:
               f1    |    f2    | Result
               7.07|     0    | 49
               7.07|     1    | 53
               7.07|     2    | 56

               and so forth....

               Let's call that table Results. If you then defined f1 and f2 as fields instead of let variables, you could use those fields as match fields in a relationship:

               YourTable::f1 < Results::f1 AND
               YourTable::f2 < Results::f2

               specify a sort order in the relationship that sorts the related records into ascending order by Result.

               And then you can use a looked up value setting or auto-enter calculation to copy the value of Results::Result into a field of YourTable instead of using this complex case function.

               PS. the data shown here looks like data derived from some kind of formula. If you can research the formula used to produce these values, it may be possible to use that formula in a calculation and that will be simpler than either this look up table or the Case function.

          • 2. Re: Case function vs look up table(s)
            laura1553

                 Thanks, Phil. 

                 I have not asked about this before, but it is a common standardized test, and scorers typically look up the scores in books of tables, so I would not be surprised if I am not the first to ask.  (I am pretty sure the formula is not obtainable; at least I have not been able to find it.)

                 Your analysis of the function is correct, and I think I understand your answer.  I'll study it more closely at work and will probably be back with questions.

                 Thanks very much!

                 Laura

            • 3. Re: Case function vs look up table(s)
              philmodjunk

                   This "feels" like data generated from a statistical function such as working with the normal curve or some such....

              • 4. Re: Case function vs look up table(s)
                laura1553

                     Hmm, good point.  I'll have to investigate the statistical function possibility further.

                     Meanwhile, on closer review, the solution you posted above makes perfect sense to me.  I'm going to test it out in our upcoming assessment period.

                     Thanks again for your help, Phil!

                • 5. Re: Case function vs look up table(s)
                  laura1553

                       Dear Phil,

                       You are officially my favorite person in the universe today.  My first look up table works perfectly, and it was super easy to set up in Excel and import into FM.  Your suggestion is going to save me many hours of tedious work.  Thank you, thank you, thank you! 

                       ~Laura