Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Lookup from a file in RECAP
Go
New
Search
Notify
Tools
Reply
  
Lookup from a file in RECAP
 Login/Join
 
<kj>
posted
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
 
Reply With QuoteReport This Post
<kj>
posted
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
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 226 | Registered: June 08, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Lookup from a file in RECAP

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.