Impromptu report

[Pr07]

Hi All,

I need to build a field in impromptu report as follows:

F1 F2 F3 F4 Reflected F4
________________________________________

a1 r1 b1 low low
a1 r1 b2 lOW low
a1 r2 b1 low high
a1 r2 b2 high high
a1 r2 b3 low high
a2 R1 b1 high high
a2 r2 b2 null high


***********

The problem is how to build 'Reflected F4' from a set of F1,F2,F4.

Greatly appreciate your help on this.

Regards

 

[drlex]

Pr07,
What logic dictates the value of the Reflected F4 field?

On the given example, it looks to be low unless F2 is r2 or F4 is high, which would require Reflected F4 = IF (F4 = 'high') THEN ('high') ELSE IF (F2 = 'r2') THEN 'high' ELSE 'low'

I guess it must be more involved than this

lex

"Time flies like an arrow, but fruit flies like a banana."

 

[Pr07]

Thanks Lex. My apologies for presenting incomplete problem in a rush to get a solution.
Here are the details:

For a single F1, there can be multiple occurences of F2 and likewise multiple F3 for a single F2. F4 is actually an attribute of F3(values with varying level of severity not just 'High' & 'low'). The most severe level of F4 within a single F2 goes to Reflected F4.

I am able to get to it by assigning severity level to every F4 value, then picking max level for F1,F2 and then translating it back to character string(instead of numbers).
I am concerned that with the amount of data we have to deal with, this might be very inefficient. Please advise me with a better way to do this.

Thanks

 

[drlex]

Pr07,
Unfortunately, it sounds (reads?) as though you need to read in all values in order to determine the value in F4 reflected.
It follows that you would need to ensure good indexing and fast access. Is there any scope for datawarehousing to store either all data to reduce access time or just historic maximum and minimum values?
lex

"Time flies like an arrow, but fruit flies like a banana."

 

[Pr07]

Hi Lex,

A star-schema datamart is the source for F1-F4. But Reflected F4 logic is not build in it and I am generating that in .imr.
Comments??

Thanks

 

[nagrajm]

Pr07

Your method is one way to do it which I don't think should be inefficient, if that is the only way to accomplish the result in impromptu? How long does it take?

Another method would be to create a database view (with the same logic that you would in creating the impromptu report) of all those columns from F1...Reflected F4 and pull a report from the view.

The difference between the view and the impromptu be that the view would be processed entirely on the database whereas depending upon how you create the Impromptu report, the processing may be local causing performance degradation.

You will have to try both methods to see what works for you.

Nagraj

 

[Pr07]

Thanks Nagraj.

I dont know the time this report will take as I have been working with the sample data set only. But the actual data would be over a million records and potential of growing exponentially with time. Thats the reason for concern.Maybe view would be a better option.

Regards