Assign leanest value 1

[Chuku]

I have two types of processes like A and B.

say, If there are 5 processes totally

Sample data:
Process Type RecoveryTime
Proc1 A 4hours
Proc2 A 5days
Proc3 B 8hours
Proc4 B 3days
Proc5 A 12hours

I need to assign the leanest value i.e (4hours here) to a field called PRT. I should consider both the process types when i assign the leanest value. Process A and B are from different tables

How do I do that?

Thanks in advance.

 

[Madawc]

First, link A and B as a detail line. If necessary, use grouping to avoid duplicates.

Create a formula field that will show the lower of the two values.

Find the minimum for this field.

You'll need to convert days to hours, obviously.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Chuku]

Sorry but how do i create a formula to show the lower of the two values

 

[Madawc]

Minimum is the lower of two or more values

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[MCuthill]

Chuku,

Help in Crystal will provide a more detailed explanation, but the function is as follows:

To obtain the minimum value on the entire report:

Minimum({YourField})

To obtain the minimum value within a group:

Minimum({YourField},{GroupField})

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

If the values for Type are coming from two different fields from different tables, then how do the fields actually display when you place them in the detail section of your report?

When you mention showing the lower of TWO values, what are you referring to?

-LB

 

[Chuku]

Lbass,

I will explain in detail below

In my tables, there are 2 types of processes, Business and Baseline.

Business process has a recovery time while Baseline process do not have a recovery time. They need to be related to their Business process to have a recovery time (which is present in another table)

so the consolidated minimum value(recovery time) needs to be calculated.

So the data would be like
Process Type Recov Time
Proc1 Business 4hours
Proc2 Business 5days
Proc3 Baseline Null ( but their related Business process has values like 8 hours, 2 days )

I need to consider all these 4 values and pick the minimum out of it.

Hope i am clear now.

 

[Madawc]

If you assemble values in a formual field, you can apply Minimum to that.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

So how are you adding the info from the other table? If you linked to that table, you should have shown the detail section display that includes that field. Or are you planning to add those values in a subreport?

If it is just another field on the report, then you could do create a formula like this:

minimum([minimum({@recovtime1},{table.groupfield}),minimum({@recovtime2},{table.groupfield})])

...where each recovtime formula converts days to hours, and where recovtime1 is from one table, and recovtime2 is from another.

-LB

 

[Chuku]

Lbass,
I have links to the other table. Also the recovery time is a string field. Do I still need to convert the days to hours ?

 

[lbass]

You can't calculate using a string--you have to convert it to a number, and yes, you need to convert days to hours. Does the field actually display exactly as shown in your first thread? Are there any other intervals besides days and hours that can be in the field?

-LB

 

[Chuku]

Lbass,
No. The field just has days and hours.

 

[Chuku]

Below is the sample data from the field,

3 Days
4 Hours
NULL
12 Hours
7 Days
24 Hours
> 7 Days and < 60 Days

 

[lbass]

Can you clarify whether these are fields that can ONLY have these values or could there be variations when a range is indicated, e.g., are these ranges user entered so that they could create a custom range like ">9 Days and < 82 Days"? If they are standard, please show all possibilities.

-LB

 

[Chuku]

The below are the ONLY possible values.
3 Days
4 Hours
NULL
12 Hours
7 Days
24 Hours
> 7 Days and < 60 Days

 

[lbass]

Then I would just set up a formula like this:

//{@recovtime1}:
if isnull({table1.recovtime}) then
0 else (
select {tableA.recovtime}
case "4 Hours" : 4
case "12 Hours" : 12
case "24 Hours" : 24
case "3 Days" : 72
case "7 Days" : 168
case ">7 Days and < 60 Days" : 192
)

Create the same formula for table2.recovtime. Then use the formula:

minimum([minimum({@recovtime1},{table.groupfield}),minimum({@recovtime2},{table.groupfield})])

-LB

 

[Chuku]

Thanks for the advice.. I tried the above way. But it is displaying a value of 0.00 for the last formula

 

[lbass]

Sorry, change the beginning of each formula to:

if isnull({table1.recovtime}) then
999 else

-LB

 

[Chuku]

Lbass,
I was able to get the minimum value. But when the minimum value is ">7 Days and < 60 Days" , it displays 0.00.
It is displaying correctly for other data

 

[lbass]

That suggests that the display is not exactly:

">7 Days and < 60 Days"

Add a space between > and 7, which I inadvertently omitted (sorry):

"> 7 Days and < 60 Days"

If it still doesn't work, check the display in other ways and ensure that it exactly matches.

-LB