12 Replies Latest reply on Aug 28, 2016 3:05 AM by ucharles

    Extending a "case" calculation

    paulatkins

      This Case statement works well, but I want to run another test in the same calculation and I do not know what function to use and how to combine them.

       

      The Case statement looks for data in any of the three fields, we use it to import from files supplied by schools, some schools format the first and last names of students differently. For example "Lastname, Firstname" and "Firstname, Lastname" and when the two are in separate fields.

       

      Case

      (

      DataType = "Last, First (eg: Smith, Jane)" ; TextStyleAdd (Right ( SchoolName_LastCommaFirst ; Length ( SchoolName_LastCommaFirst ) - Position ( SchoolName_LastCommaFirst ; ", " ; 1 ; 1 ) - 1 ) ; Titlecase) ;

      DataType = "First, Last (eg: Jane, Smith)" ; TextStyleAdd (Left ( SchoolName_FirstCommaLast ; Position ( SchoolName_FirstCommaLast; ", " ; 1 ; 1 ) - 1 ) ; Titlecase) ;

      DataType = "First and Last name in separate columns" ; TextStyleAdd ( SchoolName_First ; Titlecase )

      )

       

      I need to add to it the option to this calculation that if a global field "Use Preferred Name" is set to “Yes”, then use data in a "Preferred First Name" field, if that field is empty, then run the above Case statement. If the global field is not set to “Yes”, then just run the Case statement.

       

      Thank you for your consideration.

        • 1. Re: Extending a "case" calculation
          jbrown

          Morning.

          You could combine this into an IF statement:

           

          IF (UsePerferredName = "Yes" ; <<Use data in preferred name field>>  ;

           

          Case ( ….

           

           

          )

           

          )

           

          Use the IF statement to decide to use the preferred name or run the case statement. Be sure to surround the close out the IF statement after the case statement.

           

          EDIT: OR

          Put the "UsePreferredName = 'Yes'" as the first statement in your case statement. Since case statements run until one row is true, it will stop if the global field is "Yes". Otherwise it will keep running.

          • 2. Re: Extending a "case" calculation
            paulatkins

            Thank you Jeremy for your reply. Good evening/morning.

             

            I've just tried that, but if the global is set to "Yes" and the preferred name field is empty, it does not pick up the Case statement and leaves the field blank, I need to calculation to proceed and look up the correct name based on the datatype. When it is not set to "Yes" it successfully picks up the right Datatype in the Case statement.

             

            Here is the calculation as you suggested (If I have it correct!)

             

            If (Use_Preferred_g = "Yes" ; SchoolName_Preferred ;

             

             

            Case

            (

            DataType = "Last, First (eg: Smith, Jane)" ; TextStyleAdd (Right ( SchoolName_LastCommaFirst ; Length ( SchoolName_LastCommaFirst ) - Position ( SchoolName_LastCommaFirst ; ", " ; 1 ; 1 ) - 1 ) ; Titlecase) ;

            DataType = "First, Last (eg: Jane, Smith)" ; TextStyleAdd (Left ( SchoolName_FirstCommaLast ; Position ( SchoolName_FirstCommaLast; ", " ; 1 ; 1 ) - 1 ) ; Titlecase) ;

            DataType = "First and Last name in separate columns" ; TextStyleAdd ( SchoolName_First ; Titlecase )

            )

            )

             

            Do you have an idea what I am doing wrong?

            • 3. Re: Extending a "case" calculation
              jbrown

              Ah. I missed the "And the perferred name can't be empty".

               

              Adjust the beginning of your IF statement to say :

               

              If (Use_Preferred_g = "Yes" AND Not IsEmpty (SchoolName_Preferred) ; SchoolName_Preferred ;

               

              Case (…)

               

              )

              • 4. Re: Extending a "case" calculation
                paulatkins

                You're a gem thank you Jeremy!

                 

                Thank you so much.

                • 5. Re: Extending a "case" calculation
                  erolst

                  Hi Paul –


                  here's a somewhat more structured approach to reading the desired name:


                  Case (

                    not IsEmpty ( "Use Preferred Name" ) ;

                    "Preferred First Name" ;

                    Let ( [

                      d = DataType ;

                      typeIsLF = d = "Last, First (eg: Smith, Jane)" ;

                      typeIsFL = d = "First, Last (eg: Jane, Smith)" ;

                      typeIsSep = d = "First and Last name in separate columns" ;

                      lastFirst = SchoolName_LastCommaFirst ;

                      firstLast = SchoolName_FirstCommaLast ;

                      theName =

                        Case (

                          typeIsLF ;

                            GetValue ( Substitute ( lastFirst ; ", " ; ¶ ) ; 2 ) ;

                          typeIsFL ;

                            GetValue ( Substitute ( firstLast ; ", " ; ¶ ) ; 1 ) ;

                          typeIsSep ;

                            SchoolName_First

                        ) // end inner Case()

                      ] ;

                      TextStyleAdd ( theName ; Titlecase )

                    ) // end Let()

                  ) // end outer Case()

                  1 of 1 people found this helpful
                  • 6. Re: Extending a "case" calculation
                    paulatkins

                    Erolst, thank you. Does such a structure improve the performance of the calculation, or is it better 'code' to run within just the Case calculation without the if?

                    • 7. Re: Extending a "case" calculation
                      erolst

                      Hi Paul –

                       

                      Case() can do everything If() can, so I simply don't use If(); and if you ever want add more test/result pairs to Case(), you can just do that, while If() is limited to just one test, so you'd have to change it to Case() …

                       

                      In this example, you could have used If() instead of the outer Case(), but not the inner one.

                       

                      As to the performance: in a simple calc like this, the difference between your version and this one using Let() should not be noticeable (the story would be different if you reference related fields and/or function results over and over again …)

                       

                      But I like to think that it increases the coder's performance (yours & mine …), because a) the calculation is clearer in regard to the test conditions, b) if you need to make changes, you can perform them at just a single place (.e.g. change a referenced field, or the formatting of the output), and c) it's easier to read overall.

                      • 8. Re: Extending a "case" calculation
                        paulatkins

                        Excellent, thank you, that makes sense.

                        • 9. Re: Extending a "case" calculation
                          Extensitech

                          Just a verbose agreement with erolst:

                           

                          Designing for Extensibility - Extensitech | Extensitech

                           

                          Chris Cain

                          Extensitech

                          • 10. Re: Extending a "case" calculation
                            erolst

                            And here I thought I already was being verbose …

                             

                            Thanks for sharing.

                            • 11. Re: Extending a "case" calculation
                              Extensitech

                              It seems I never use a sentence when a few pages will do the trick. ;-)

                               

                              Chris Cain

                              Extensitech

                              • 12. Re: Extending a "case" calculation
                                ucharles

                                Hi Erolst, how to I extend my formula in the same way:

                                 

                                class =
                                Case (
                                age < 365 and sex = "Female" ; "Calf-Female" ;
                                age < 365 and sex = "Male" and Castration = "Yes" ; "Calf-Male" ;
                                age < 365 and sex = "Male" and Castration = "No" ; "Calf-Male" ;


                                age < 730 and sex = "Female" ; "Heifer" ;
                                age < 730 and sex = "Male" and Castration = "Yes"; "Steer/Tollie" ;
                                age < 730 and sex = "Male" and Castration = "No"; "Young Bull" ;


                                age ≥ 730 and sex = "Female" ; "Cow" ;
                                age ≥ 730 and sex = "Male" and Castration = "Yes" ; "Ox" ;
                                age ≥ 730 and sex = "Male" and Castration = "No" ; "Bull";)

                                 

                                to include Let CowHeifer = "Cow" or "Heifer" so that I will be able to use "CowHeifer" the same way that I would the others.