Focal Point
Lookup from a file in RECAP

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6021077331

February 12, 2004, 03:35 PM
<kj>
Lookup from a file in RECAP
Is there a way to lookup a file from RECAP and display the value in subfoot?
I have a summary row that displays department summary including no of employees, total sal.
Ranks for the salary are defined in a seperate table(rank 1 if sal 0 - 100,000, rank 2 if sal 100,001 - 200,000 etc..) I need to pickup the appropriate rank for each department depends on dept total sal.

Thanks in advance.
kj
February 12, 2004, 06:44 PM
<kj>
I forgot to mention the environment. I am using WebFocus 5.2.1 on NT.
Where-based joins will work with a table request. But the values are generated with a RECAP statement on SUBFOOT on BY field.

Too make it clear I will explain with an example.

rank table
low sal high sal rank
0 15,000 6
15,001 20,000 1
20,001 30,000 2
30,001 50,000 3
50,001 100,000 4


deptno Emp no Salary rank
1 11111 20,000 1
22222 21,000 2
33333 45,000 3
44444 12,000 6
dept1 avg 24,500 2
2 55555 10,000 6
66666 14,000 6
77777 55,000 3
dept2 avg 26,333 2

I could display rank for each employee with a WHERE-BASED join.
I could not get the ranks for dept summary.
Any kind of help would be greatly appreciated.

Thanks,
kj
February 15, 2004, 07:56 PM
GCohen
This may be an example where you should compute all your values first in two sentences. eg
SUM AVE.SALARY AND COMPUTE DRANK/I3=IF AVE.SALARY LE 15000 THEN 6 ELSE IF AVE.SALARY LE 50000 THEN 4 ELSE..
BY DEPARTMENT
SUM SALARY AND COMPUTE PRANK/I3=IF SALARY LE 15000 THEN 6 ELSE IF SALARY LE 50000 THEN 4 ELSE ...
BY DEPARTMENT BY EMPLOYEE
...
You can NOPRINT some fields and replace them in a RECAP, or you can HOLD the data set and do your report from the HOLD file.