Help Determining Fields within a Range and Displaying the Results

[omcompany]

My apologies for the title, it was a hard one to summarize.

I am using CR 2008.

Here is an example of my data and what I am trying to accomplish.

({table.Weight}, {table.Time}, {table.X}, {table.Y}, {table.TimeDiff})
(100, 0 , A , 1 , 8)
(100, 25 , A , 2, 8)
(200 , 92 , B , 1, 8)
(150, 125 , B , 2, 8)
(180, 30 , B , 3, 8)
(200, 100 , B , 4, 8)
(50, 192 , B , 5, 8)

{table.TimeDiff} = 8 (the TimeDiff is set by the user)

I need to determine the maximum weight (sum if more than one record) for any record(s) that have a timing difference of {table.TimeDiff} or less.

Then I need to display the X and Y for the record(s).

I have created a formula,

//@Label
{table.X} + ToText({table.Y},0)

to use to display the results.

The report will need to look at each record, determine which ones are within the TimeDiff value, total the Weights for the results and display the @Label(s) with the maximum combined Weight.

The results from the above data should be; B1, B4

A2 and B3 meet the {table.TimeDiff} requirement but the sum of their weight is less than B1, B4.

There will be data sets where the TimeDiff is not met and a single record will be the result. The result should be @Label with the Max ({table.Weight}). If there is no Max({table.Weight}) the result should be FirstRecord({@Label}).

Thanks in advance for any assistance. The help is appreciated.

 

[IanWaterman]

Using the data set above please show results you would expect to see.

Ian

 

[omcompany]

Thank you for your time.

The results from the above data should be; B1, B4

As a side note, A2 and B3 meet the {table.TimeDiff} requirement but the sum of their weight is less than B1, B4.

 

[IanWaterman]

I still do not understand your logic.

Still can not see why B1 and B4 are returned compared to any of the others

Please expalin how you reach this conclusion

A2 and B3 meet the {table.TimeDiff} requirement

why dont A1, B2 and B5?

Ian

 

[omcompany]

The {table.TimeDiff} requirement in the above case is 8 so the Time difference between any of the records that is 8 or less would be considered. The final result would be the records who's sum is the greatest.

B1 has a Time of 92
B4 has a Time of 100
Difference of 8 which meets the TimeDiff requirement.
Total Weight of B1, B4 is 400


A2 has a Time of 25
B3 has a Time of 30
Difference of 5 which meets the TimeDiff requirement.
Total Weight of A2,B3 is 280

Since the total weight of B1,B4 is greater than A2,B3 it would be the desired result.

Hope that helps. It is not an easy one to explain. Your patience is appreciated.

 

[IanWaterman]

Does this mean you have to compare every record ie A1 to all the other records, then A2 to all the other records, then B1 to all the other records.

That would require 21 comparisons?

This will be very difficult to achieve in crystal and would require you to have a known number of cases so you can do set up each permutation.

Ian

 

[omcompany]

Yes. Each record has to be compared to all records to get the difference in time. You would then use that difference result to see if it meets the TimeDiff criteria. There could be hundreds of records with multiple sets of records that meet the TimeDiff criteria. You would then take the maximum weight of those sets of records to get the X,Y (s) result. If it were to compare only the next or previous record I believe I could have done that but this was a few steps beyond that. I understand if it can not be done or at least efficiently. I do appreciate you looking into it though.

 

[IanWaterman]

You might be able to do a cartesian join by aliasing the table and effectively joining each row to each other and then do the comparison you want.

You might have to do this as a command as Crystal may not let you create a cartesian join.

Ian

 

[IanWaterman]

A command would look something like this and for dataset above would return 21 records, the addtional where clause should filter that to two.

select * from (
select weight, time, X, Y, timediff
from table
where test = 1) R
left outer join
(select weight, time, X, Y , timediff
from table
where test = 1) p
on r.X <> p.X
and r.y <> p.y
where p.time - r.time <= r.timediff

In report you can group and sum weights to display record you want.

Ian

 

[omcompany]

Thank you very much for your time. I will give your suggestion a try.

 

[CoSpringsGuy]

So I read over this and took it on as a challenge I spose... I created the formula below which seems to work except when no records meet the criteria. I will have to keep thinking that through but I thought I would throw out the formula and see if it does what you want on a larger set of data. For the following code to work, your records must be sorted on {table.Time} in Ascending order..

NOTE: All my feilds begin with Sheet1 because I created an excep spreadsheet to test with so you will have to adjust that reference to match your data.

numbervar flag := 0; numbervar array CA; Redim preserve CA[3];
numbervar array PA ; Redim preserve PA[4]; stringvar output2;
stringvar array CXY; Redim preserve CXY[2]; stringvar array PXY; Redim preserve PXY[2];
if recordnumber = 1 then (PA[1] := 999999999;CA[1] := 999999999);
if ({Sheet1_.table#time}-previous({Sheet1_.table#time})) <= {Sheet1_.tabletimediff} then 
(
    if {Sheet1_.table#weight} < CA[1] and flag = 0 then
    (
        CA[1]:= {Sheet1_.table#weight};
        CA[2]:= previous({Sheet1_.table#weight});
        CXY[2] := previous({Sheet1_.tablex})&totext(previous({Sheet1_.tabley}),"#");
        CXY[1] := {Sheet1_.tablex}&totext({Sheet1_.tabley},"#");
        flag := 1
    );
    if {Sheet1_.table#weight} < CA[1] and flag = 1 then
    (
        CA[1]:= {Sheet1_.table#weight};
        CXY[1] := {Sheet1_.tablex}&totext({Sheet1_.tabley},"#");
    );
    if {Sheet1_.table#weight} < CA[2] and {Sheet1_.table#weight} > CA[1] then
    (
        CXY[2] := {Sheet1_.tablex}&totext({Sheet1_.tabley},"#");
        CA[2]:= {Sheet1_.table#weight};
    );
)
else if ({Sheet1_.table#time}-previous({Sheet1_.table#time})) > {Sheet1_.tabletimediff} then
(
if CA[3] < PA[1] then
(
PA[1] := CA[1]+CA[2];
PXY[1] := CXY[1];
PXY[2] := CXY[2];
);

flag := 0;
);
CA[3] := CA[1]+CA[2];
output2 := PXY[1] &", " & PXY[2]&", " & totext(PA[1],"#");

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[omcompany]

Very nice, thank you. I will be able to take a look on Monday.

 

[lbass]

How do you know which record is "first"--your requirements imply there is a sequential field, e.g., a record ID or datetime stamp that determines the record order. So what field tells you that a record is first?

-LB

 

[omcompany]

LB,

Thank you for your time.

The records are grouped by //@Label({table.X} + ToText({table.Y},0)). Their records are in order of X,Y.

For a bit more information/clarification, the record, {table.Time} represents an assigned time (simply a number) not a timestamp.

Also, for what it is worth you helped me with a similar issue, although not as complex, about a year ago.

http://www.tek-tips.com/viewthread.cfm?qid=1659386

 

[CoSpringsGuy]

So could you let me know what failed on the code above? In theory it should work and I tested as well as I could..

Basically if you sort records by {table.time} It will cause the records that meet your criteria to occur in succession and accumulate the data (x,y of both records and weight) required to out put your results. If there are more than 2 in a row, the code will evaluate the weight of the two smallest and compare to the previous "group" of records which meet the requirement. If there are only two it adds those together and saves the total weight to compare to the next "group" of records that meet your requirement.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

by the way ... the formula needs to be placed in both the detail section and the footer ... it can be suppressed in the detail section but it must be there.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

before you answer that ........... I reread your post ... you needed the higher total weight ... I had to modify the code for that ...

numbervar flag := 0; numbervar array CA; Redim preserve CA[3];
numbervar array PA ; Redim preserve PA[4]; stringvar output2;
stringvar array CXY; Redim preserve CXY[2]; stringvar array PXY; Redim preserve PXY[2];
//if recordnumber = 1 then (PA[1] := 999999999;CA[1] := 999999999);
if ({Sheet1_.table#time}-previous({Sheet1_.table#time})) <= {Sheet1_.tabletimediff} then 
(
    if {Sheet1_.table#weight} > CA[1] and flag = 0 then
    (
        CA[1]:= {Sheet1_.table#weight};
        CA[2]:= previous({Sheet1_.table#weight});
        CXY[2] := previous({Sheet1_.tablex})&totext(previous({Sheet1_.tabley}),"#");
        CXY[1] := {Sheet1_.tablex}&totext({Sheet1_.tabley},"#");
        flag := 1
    );
    if {Sheet1_.table#weight} > CA[1] and flag = 1 then
    (
        CA[1]:= {Sheet1_.table#weight};
        CXY[1] := {Sheet1_.tablex}&totext({Sheet1_.tabley},"#");
    );
    if {Sheet1_.table#weight} > CA[2] and {Sheet1_.table#weight} < CA[1] then
    (
        CXY[2] := {Sheet1_.tablex}&totext({Sheet1_.tabley},"#");
        CA[2]:= {Sheet1_.table#weight};
    );
)
else if ({Sheet1_.table#time}-previous({Sheet1_.table#time})) > {Sheet1_.tabletimediff} then
(
if CA[3] > PA[1] then
(
PA[1] := CA[1]+CA[2];
PXY[1] := CXY[1];
PXY[2] := CXY[2];
);
flag := 0;
CA[1] := 0 ;
CA[2] := 0 ;
);
CA[3] := CA[1]+CA[2];

output2 := PXY[1] &", " & PXY[2]&", " & totext(PA[1],"#");

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[omcompany]

The code above worked with the sample data as well as several other sets.

I had to make a very slight change due to my mistake. I stated earlier that the Time Difference criteria was "less than or equal to" but it is only less than. I changed,
({Sheet1_.table#time})) <= {Sheet1_.tabletimediff to ({Sheet1_.table#time})) < {Sheet1_.tabletimediff


Where it broke was;

If there were more than two records in a set it would not include the weight of the third record sometimes giving the incorrect set.


({table.Weight}, {table.Time}, {table.X}, {table.Y}, {table.TimeDiff})
(100, 0 , A , 1 , 8)
(100, 7 , A , 2, 8)
(80 , 25 , B , 1, 8)
(80, 25 , B , 2, 8)
(80, 25 , B , 3, 8)
(200, 167 , B , 4, 8)
(50, 192 , B , 5, 8)

{table.TimeDiff} = 8 (the TimeDiff is set by the user)

The desired results from the data would be,

B1, B2, B3 with a total weight of 240.

The results now are A1, A2, 200.

The set of records that meet the TimeDiff criteria could be any number of records long.


Also, if there are no sets of records that meet the TimeDiff criteria then the Label of Maximum Weight needs to be displayed.

(100, 0 , A , 1 , 8)
(100, 25 , A , 2, 8)
(200 , 92 , B , 1, 8)
(150, 117 , B , 2, 8)
(180, 142 , B , 3, 8)
(200, 167 , B , 4, 8)
(50, 192 , B , 5, 8)

The results from the above data should be, B1.

There is another record, B4 that has equal weight but only one record needs to be displayed.

Thank you very much for your efforts on this. I am learning from it.

 

[CoSpringsGuy]

We can still make it work.. Got another project at work ahead of this one but I will have something to you in an hour or so


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

omcompany -

I believe this can be done using Crystal and I kind of like the challenge but I just dont have time to make this a priority now. I worked on it a little today and made some progress but it will be a while before I can spend anymore time on it. I will address it again if you still have the need but please post here if you find a work around or another solution.



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection