14 Replies Latest reply on Apr 27, 2017 3:12 AM by manuelruizga

    Case function with field greater than a number but lower than another number

    manuelruizga

      I have set a calculation field called "retraso_horas" which calculates the elapsed time between two dates and hours which are also set in 4 different fields. It works just fine. For example, it shows 3 when the elapsed time has been 3 hours. This is the function used (The result is shown as "Time"):

       

      ((f_real_llegada - f_prog_llegada) * 86400) + h_real_llegada - h_prog_llegada

       

      Now, I am building a case function in another field so that, in one of the cases, if the elapsed time is equal or greater than 3 hours but lower than 4 hours, then show something. Yet, it seems like it is not working well. In particular, it does not work well with the last two cases, "case 5 and case 6". It will show only "300", even when "retraso_horas" is greater than 4 hours. I think it is only taking into account (retraso_horas  ≥  3). This is how I have set it:

       

      Case (

      //CASE 1//

      (extracomunitarios = "intracomunitarios") and (rango_distancia = "menos de 1.500km") and (retraso_horas  ≥  3); 250;

       

      //CASE 2//

      (extracomunitarios = "intracomunitarios") and (rango_distancia = "más de 1.500km dentro de la EU") and (retraso_horas  ≥  3); 400;

       

      /CASE 3//

      (extracomunitarios = "extracomunitarios") and (rango_distancia = "menos de 1.500km") and (retraso_horas  ≥  3); 250;

       

      //CASE 4//

      (extracomunitarios = "extracomunitarios") and (rango_distancia = "entre 1.500 - 3.500km") and (retraso_horas  ≥  3); 400;

       

      //CASE 5//

      ((extracomunitarios = "extracomunitarios") and (rango_distancia = "más de 3.500km") and (3 ≤ retraso_horas < 4)); 300;

       

      //CASE 6//

      ((extracomunitarios = "extracomunitarios") and (rango_distancia = "más de 3.500km") and ( retraso_horas  ≥  4)); 600;

       

      )

       

      Is there anything wrong I am missing?

       

      Thank you in advance.

        • 1. Re: Case function with field greater than a number but lower than another number
          philmodjunk

          I see the wrong inequality operator for case 5.

          • 2. Re: Case function with field greater than a number but lower than another number
            TomHays

            Instead of

            //CASE 5//

            ((extracomunitarios = "extracomunitarios") and (rango_distancia = "más de 3.500km") and (3 ≤ retraso_horas < 4)); 300;

             

            I think you need

             

            //CASE 5//

            ((extracomunitarios = "extracomunitarios") and (rango_distancia = "más de 3.500km") and (3 ≤ retraso_horas) and (retraso_horas < 4)); 300;

             

            -Tom

            • 3. Re: Case function with field greater than a number but lower than another number
              fitch
              Case (
              
              //CASE 1
                  extracomunitarios = "intracomunitarios" and retraso_horas  ≥  3 ;
                  Case(
                      rango_distancia = "menos de 1.500km" ; 250;
              //CASE 2
                      rango_distancia = "más de 1.500km dentro de la EU" ; 400
                      );
              
              //CASE 3
                  extracomunitarios = "extracomunitarios" ; 
                  Case(
                      rango_distancia = "menos de 1.500km" and retraso_horas  ≥  3; 250;
              //CASE 4
                      rango_distancia = "entre 1.500 - 3.500km" and retraso_horas  ≥  3; 400 
              //CASE 6
                      rango_distancia = "más de 3.500km" ;
                      Case(
                          retraso_horas  ≥  4 ; 600
              //CASE 5
                          retraso_horas  ≥  3 ; 300 
                          )
                      )
                  )
              
              
              
              

               

              Notice that I switched cases 5 and 6 in order to simplify the logic a little bit. It's possible this could be further simplified.

              • 4. Re: Case function with field greater than a number but lower than another number
                ch0c0halic

                You are correct, the way it is written the controlling Value is "retraso_horas  ≥  3". None of the other values are meaningful in tis calc.

                 

                If value => 3 is yes the cases 1-4 will be true and 5 & 6 will never be used.

                If value < 3 then it is also less than 4, so neither 5 or 6 are true either.

                 

                 

                Evaluating a case statement should be done very carefully. In your case (pun not intended) the different possibilities of the fields cover everything they could be while retraso_horas  ≥  3 is constant. This makes the highest level of differentiation be "retraso_horas  ≥  3", which is the same on Case 1-4.

                 

                You also haven't designated a default value. for retraso_horas < 3

                 

                retraso_horas has 2 values used as < 3, between 3 and 4, and > 4

                 

                rango_distancia has four values:

                1. menos de 1.500km,

                2. más de 1.500km dentro de la EU,

                3. entre 1.500 - 3.500km,

                4. más de 3.500km

                Question: Is 1.500km the significant part of the value or is the wording also important? Are answers 1 and 2 actually the same thing with different wording?

                Are these the only values in the field?

                Supposition: If it's just the number part that is significant then there are only 2 tests and one default.

                < 1.500km

                3.00km

                Note that with the Case() you make each test exclude the values below so they only have to test one value.

                If the value is <1.500km then it cannot be > 1.500km or >3.00km

                If the value is > 3.00km we do not have to check if it is >1.5km

                Between the two is the default because it has already failed the first two tests.

                 

                 

                extracomunitarios has two values:

                1. intracomunitarios

                2. extracomunitarios

                But are they necessary to the Case()? Are they unnecessary because the other two values properly designate the hierarchical selection?

                 

                We have to evaluate each value for efficacy.

                In the current Case():

                1. Only one test is for retraso_horas between 3 and 4.

                Which of the other values matter. Or are they also excluded by the selection process only allowing them if the value isn't between 3 and 4?

                 

                2. The combinations of intracomunitarios and rango_distancia appear to be very close in numeric values. Possibly the values are hierarchical? Can intracomunitarios be eliminated?

                 

                3. Case 1 and 3 result in 250.

                Case 2 and 4 result in 400

                Case 5 results in 300

                Case 6 results in 600

                 

                We should at least combine the test together so each result is only required once. Meaning we should probably only have 4 test cases.

                 

                You will need to evaluate the User interface to determine the if any of the fields have values you have not allowed for and if there are combinations not possible because of hierarchical data input.

                 

                I hope that helps. This is very difficult to analyze without knowing more about the UI and the data structure and what it all means.

                Sorry, if I could read the language I might not be asking some of the questions. Then I'd know what the answers meant.

                1 of 1 people found this helpful
                • 5. Re: Case function with field greater than a number but lower than another number
                  manuelruizga

                  Thank you all for your answers.

                   

                  I will try to clarify the meaning of this code as I still have the problem.

                   

                  This case function is meant to give the compensation for a flight delay which will result in one of these posible answers 1) 250 2) 400 3) 300 and 4) 600 depending on the values of 3 other fields, "extracomunitarios", "rango_distancia" and "retraso_horas". All these 3 fields are calculation fields which will result in the following values.

                   

                  A) "extracomunitarios" has only two posible values 1)intracomunitarios and 2) extracomunitarios. This calculates if a flight is within the Europen Union.

                   

                  B) "rango_distancia" has 4 posible values 1) menos de 1.500km 2) más de 1.500km dentro de la EU 3) entre 1.500 - 3.500km and finally 4) más de 3.500km. In this case, wording is important as this calculation field will show any of the previous values. This field will calculate distances in between the two airports in a flight.

                   

                  C) "retraso_horas" could be any value. It is meant to calculate a delay in a given flight.

                   

                  I have tried Tom's code but I still get the same problem. I have a test flight set to have a 3 hours delay but the calculation gives me a 600 answer. As 3 hours is within this range(3 ≤ x < 4) the expected answer should be 300. However it shows 600. The problem is in the whole //CASE 2.3 from the following code:

                   

                  Case ( 

                   

                  //CASE 1 INTRACOMUNITARIOS

                  extracomunitarios = "intracomunitarios";  //CASE 1.1

                                                            Case(rango_distancia = "menos de 1.500km" ;  //CASE 1.1.1

                                                                                                                                         Case (retraso_horas ≥ 3; 250;

                   

                   

                                                                                                                                       //CASE 1.1.2

                                                                                                                                        retraso_horas < 3 ; 0);

                   

                   

                                                            //CASE 1.2

                                                                rango_distancia = "más de 1.500km dentro de la EU" ; //CASE 1.2.1

                                                                                                                                                         Case (retraso_horas ≥ 3; 400;

                   

                   

                                                                                                                                                        //CASE 1.2.2   

                                                                                                                                                         retraso_horas < 3 ; 0)

                                                               ); 

                   

                  //CASE 2  EXTRACOMUNITARIOS

                  extracomunitarios = "extracomunitarios";

                                                             //CASE 2.1

                                                             Case( rango_distancia = "menos de 1.500km"; //CASE 2.1.1

                                                                                                                                         Case (retraso_horas ≥ 3; 250;

                                                                                                         

                                                                                                                                        //CASE 2.1.2

                                                                                                                                         retraso_horas < 3 ; 0); 

                   

                                                             //CASE 2.2

                                                             rango_distancia = "entre 1.500 - 3.500km"; //CASE 2.2.1

                                                                                                                                   Case (retraso_horas ≥ 3; 400;

                   

                   

                                                                                                                                  //CASE 2.2.2

                                                                                                                                   retraso_horas < 3 ; 0);

                   

                                                             //CASE 2.3

                                                             rango_distancia = "más de 3.500km"; //CASE 2.3.1

                                                                                                                            Case (retraso_horas ≥ 3 and retraso_horas < 4; 300;

                                                                 

                                                                                                                          //CASE 2.3.2

                                                                                                                           retraso_horas ≥ 4; 600;

                   

                   

                                                                                                                           //CASE 2.3.3

                                                                                                                           retraso_horas < 3 ; 0)

                                                                ) 

                         )

                  • 6. Re: Case function with field greater than a number but lower than another number
                    manuelruizga

                    Any test flight I tried matching any of the other CASES works perfect. It will only fail when trying CASE 2.3.1 . In this case, instead of showing 300 it shows 600 wich is the answer for CASE 2.3.2.

                    • 7. Re: Case function with field greater than a number but lower than another number
                      fitch

                      Your calculation is not what I posted. And you've now supplied a new condition (retraso_horas  <  3 ; 0 ) which I've put as the first test. This is important as it allows us to simplify everything a bit more:

                       

                      Case (
                           retraso_horas  <  3 ; 0 ;
                      
                           extracomunitarios = "intracomunitarios" ;
                           Case(
                                rango_distancia = "menos de 1.500km" ; 250;
                                rango_distancia = "más de 1.500km dentro de la EU" ; 400
                                );
                      
                           extracomunitarios = "extracomunitarios" ;
                           Case(
                           rango_distancia = "menos de 1.500km" ; 250;
                           rango_distancia = "entre 1.500 - 3.500km" ; 400
                           rango_distancia = "más de 3.500km" ;
                           Case(
                                retraso_horas  <  4 ; 300
                                600
                                     )
                                )
                           )
                      
                      2 of 2 people found this helpful
                      • 8. Re: Case function with field greater than a number but lower than another number
                        fitch

                        Note: it's not clear whether rango_distancia can have a value of "más de 1.500km dentro de la EU" when extracomunitarios = "extracomunitarios"; or a value of "más de 3.500km" when extracomunitarios = "intracomunitarios" for example. Or other nonsensical values.

                         

                        I mention this because if we can assume that extracomunitarios ALWAYS has one of these two values and isn't empty, then line 10 in my post above wouldn't be necessary, as what follows would be the default.

                         

                        And if we can assume that rango_distancia ALWAYS has a sensical value and isn't empty, then line 7 in my post above would be simply the default (400), and line 14 wouldn't be necessary, as what follows would be the default.

                         

                        I.e., assuming sensical, non-empty values:

                         

                        Case (
                        retraso_horas  <  3 ; 0 ;
                        
                        rango_distancia = "menos de 1.500km" ; 250;
                        
                        extracomunitarios = "intracomunitarios" ; 400 ;
                        rango_distancia = "entre 1.500 - 3.500km" ; 400
                        
                        rango_distancia = "más de 3.500km" and retraso_horas  <  4 ; 300 ;
                        
                        600
                        )
                        
                        • 9. Re: Case function with field greater than a number but lower than another number
                          manuelruizga

                          When extracomunitarios = "intracomunitarios", then rango_distancia can ONLY be "menos de 1.500km"  or "más de 1.500km dentro de la EU".

                           

                          When extracomunitaros = "extracomunitarios", then rango_distancia can ONLY be one out of 3 posibilities, "menos de 1.500km", "entre 1.500 y 3.500km" or "más de 3.500km". 

                           

                          extracomunitarios is a calculation field and will always show "intracomunitarios" or "extracomunitarios".

                           

                          rango_distancia is a calculation field. It works perfect. It shows the correct value when extracomunitarios is "intracomunitarios" or "extracomunitarios".

                           

                          Your last two posts work perfect BUT I still have the problem when retraso_horas = 3, extracomunitarios = "extracomunitarios" and rango_distancia = "más de 3.500km", it shows 600 when it should be showing 300. I think the problem is not within the case function. There might be something else here.

                           

                          retraso_horas is a calculation field with this function (result shown as time):

                           

                          ((f_real_llegada - f_prog_llegada) * 86400) + h_real_llegada - h_prog_llegada

                           

                          where f_real_llegada and f_prog_llegada are date fields and h_real_llegada and h_prog_llegada are time fields. All these fields are entered manually. They are the schedulled date and hour of an arrival flight and the real date and hour of the arrival.

                           

                          Could this be because the result in retraso_horas is shown as time? If so, how can I fix this?

                          • 10. Re: Case function with field greater than a number but lower than another number
                            manuelruizga

                            I would need retraso_horas to show the result in time as it is a needed data for other purposes.

                            • 11. Re: Case function with field greater than a number but lower than another number
                              keywords

                              I have sometimes found range parameters to be problematic myself, but the range is in fact defined by the overall setup, so is not needed. Try changing the final parameters as follows: case 2.3.1 to: Case ( retraso_horas < 3 ; 0 ); 2.3.2 to: Case ( retraso_horas < 4 ; 300 ); then 2.3.3 to:Case ( retraso_horas ≥ 4 ; 600 )

                              The logic is as follows:

                              *     if the delay value is less than 3 then the first case will be a true answer, so 2.3.2 and 2.3.3 will not be evaluated.

                              •     if the delay is 3 or more then case 2.3.1 will be a false answer, so the next case will be tried. If the delay is less than 4 then case 2.3.2 will be a true answer, so 2.3.3 will not be evaluated.

                              •     if the delay is 4 or more then both 2.3.1 and 2.3.2 will be false, so 2.3.3 will be evaluated and will, of course, be a true answer.

                              By arranging the cases in ascending order—<3, <4, ≥4—each failed test becomes the lower end of the range for the next test, so it is not necessary to reiterate that as part of the next test. This simplifies the test expression.

                              Hope that helps.

                              • 12. Re: Case function with field greater than a number but lower than another number
                                fitch

                                Yes, the time field is the problem. You can solve this in a couple of ways:

                                 

                                E.g., when your test is retraso_horas < 3

                                 

                                Hour(retraso_horas) < 3

                                ... or ...

                                retraso_horas < (3*3600)

                                1 of 1 people found this helpful
                                • 13. Re: Case function with field greater than a number but lower than another number
                                  fitch

                                  The reason your original calculation appeared to be mostly working is that you were testing if retraso_horas was greater than 3 or 4. And it was -- thousands of times greater! (Because time fields are stored as a number of seconds.)

                                  1 of 1 people found this helpful
                                  • 14. Re: Case function with field greater than a number but lower than another number
                                    manuelruizga

                                    Thank you Tom !!! That was causing the problem! It is working perfectly now!!