
1. Re: Case function with field greater than a number but lower than another number
philmodjunk Apr 24, 2017 12:10 PM (in response to manuelruizga)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 Apr 24, 2017 12:44 PM (in response to manuelruizga)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 Apr 24, 2017 4:01 PM (in response to manuelruizga)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 Apr 24, 2017 5:10 PM (in response to manuelruizga)1 of 1 people found this helpfulYou 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 14 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 14.
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.

5. Re: Case function with field greater than a number but lower than another number
manuelruizga Apr 25, 2017 8:50 AM (in response to ch0c0halic)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 Apr 25, 2017 8:58 AM (in response to 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 Apr 25, 2017 11:12 AM (in response to manuelruizga)2 of 2 people found this helpfulYour 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 ) ) )

8. Re: Case function with field greater than a number but lower than another number
fitch Apr 25, 2017 11:29 AM (in response to 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, nonempty 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 Apr 26, 2017 2:49 AM (in response to fitch)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 Apr 26, 2017 2:55 AM (in response to 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 Apr 26, 2017 3:37 AM (in response to manuelruizga)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 Apr 26, 2017 10:12 AM (in response to manuelruizga)1 of 1 people found this helpfulYes, 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)

13. Re: Case function with field greater than a number but lower than another number
fitch Apr 26, 2017 10:15 AM (in response to fitch)1 of 1 people found this helpfulThe 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.)

14. Re: Case function with field greater than a number but lower than another number
manuelruizga Apr 27, 2017 3:12 AM (in response to manuelruizga)Thank you Tom !!! That was causing the problem! It is working perfectly now!!