Multiple Array comparisions

[JonHeath]

Hi,

So far I've avoided using arrays but I really need to use one now.

- I have a load of history records (up to about 50) per client.
- Each history record has a date.
- There are loads of different histories but I am focusing on two types. For the purpose of explaining this I'll call them type "A" and type "B".
- I am grouping on client.
- I am using CR10.

What I need to do is put the date of type "A" in an array but only if the array doesn't already contain that date for a type "A" history. I need to do the same for type "B" histories but in a seperate array.

Then in the group footer I need to do a formula that checks that none of type B history dates have occured within 8 weeks of a type A history date. The result says "Yes" or "No".

I then need to reset the arrays to nothing, and do the same for the next client.

I can explain it ok, but I'm totally lost trying to implement it. Even putting the elements in the array I am struggling with.

 

[lbass]

I think you could do this without arrays. Create two formulas:

//{@Adates}:
if {table.type} = "A" then {table.date}

//{@Bdates}:
if {table.type} = "B" then {table.date}

Then create a formula {@>8weeks}:

if datediff("ww", maximum({@Adates},{table.client}),minimum({@Bdates},{table.client}) > 8 then "No" else "Yes"

I couldn't tell which result would be Yes or No, so you might have to reverse that, and I also couldn't tell which dates would be more recent--A or B, so you would want to use the most recent dates in the third part of the datediff formula, and the less recent in the second part.

-LB

 

[JonHeath]

Hi, thanks for your reply.

I wish it was that easy...problem is you can't evaluate a "Yes" or a "No" until having all the dates available to you. Your way you're not looking at all the dates, but only the date of that one record. Each client can have many history records attached to them. For this reason the formula needs to be in the footer after all records for that client have been read, and the data needs to be in an array.

Jon

 

[lbass]

No, actually. I was assuming you meant that the set of B dates had to be more than 8 weeks from the entire set of A dates. The formula would go in the group footer for client--but I guess that wasn't what you were looking for.

Do you mean that you want to compare the set of B dates to each A date to see if all B dates are outside of 8 weeks from the A date? How about explaining the direction of the date, i.e., "within 8 weeks" doesn't tell us whether A dates or B dates are more recent.

How about supplying some sample data showing how you would like the report to look--so we can see what you mean.

-LB

 

[pelajhia]

Have you looked at this?

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_arrays.pdf.asp

This will help you set up arrayA and B. After that you have a separate problem/formula that doesn't have much to do with arrays:

get the size of arrayB
and use that to make a loop which iterates through each date in arrayB to compare with all items in arrayA answering yes or no.

The doc above will defnitely get you started on the first part.
Let me know if this is the direction you need to head in.