Hi There,

I have a retrieve sentence that displays the Loan officers number but I also want to display the officer's name.                                                                                                                   I would like to lookup officer's name in another file. Both files can be related using the operator number.

The file I am using is APPLIC
I need to create a dictionary in that file to lookup the officer's name in PR.USER file.

Name is the ID  in PR.USER file.

Sample of my output

APPLIC Id.... LOAN NO.  LO_OFFICER APPLIED....   LOAN AMOUNT Sts Branch

 31786L1.2     687168          1012   15 JUN 21      1,025.00   5      6

3981L1        687182          1074   15 JUN 21        275.00   5      6

Please if anyone knows how to create this dictionary would be of great help.

Regards
Francis

------------------------------
Francis Aquila
Core Banking Support Officer
TISA
Papua New Guinea
------------------------------

Francis,

As a starting point, the manuals cover this pretty well.

The manuals can be downloaded from https://www.rocketsoftware.com/products/rocket-u2/documentation

For UniData see the 'Using UniData' manual - Chapter 5 Virtual Attribute Functions and refer the special virtual attribute functions section and refer to the TRANS function.

For UniVerse see the 'System Description' manual - Chapter 5 UniVerse File Dictionaries and refer to the I-Type Expressions section and refer to the TRANS function.

As others may mention there are other methods that can be used in a dictionary to do this as well such as using a program or using a T file conversion and you can also use an EVAL expression in the query if you don't want to create a dictionary item (but you still need how to use the TRANS function etc to do so).

If you cannot work out from the manuals how to construct the dictionary item you require then please let us know.

Regards,
Jonathan





------------------------------
Jonathan Smith
UniData ATS and Principal Engineer
Rocket Software
------------------------------

---

Hi There,

I have a retrieve sentence that displays the Loan officers number but I also want to display the officer's name.                                                                                                                   I would like to lookup officer's name in another file. Both files can be related using the operator number.

The file I am using is APPLIC
I need to create a dictionary in that file to lookup the officer's name in PR.USER file.

Name is the ID  in PR.USER file.

Sample of my output

APPLIC Id.... LOAN NO.  LO_OFFICER APPLIED....   LOAN AMOUNT Sts Branch

 31786L1.2     687168          1012   15 JUN 21      1,025.00   5      6

3981L1        687182          1074   15 JUN 21        275.00   5      6

Please if anyone knows how to create this dictionary would be of great help.

Regards
Francis

------------------------------
Francis Aquila
Core Banking Support Officer
TISA
Papua New Guinea
------------------------------

If you are using Unidata then you could create a dictionary like this assuming LO_OFFICER is the dictionary you are using in APPLIC and that it isn't mulivalued, if it is Mv. then the OCONV statement would need changing.  You would also need the change the 5 at the end of the OCONV line to whatever attribute the officers name is on in the PR.USER file.
001 V
002 OCONV(LO_OFFICER, "TPR.USER;X;;5")
003
004 Officer Name
005 30L
006 S
Obviously 001-006 is just the attributes in the dictionary

------------------------------
Mike Young
Senior PS Techical Consultant
Aptean - Sanderson
------------------------------

---

Hi There,

I have a retrieve sentence that displays the Loan officers number but I also want to display the officer's name.                                                                                                                   I would like to lookup officer's name in another file. Both files can be related using the operator number.

The file I am using is APPLIC
I need to create a dictionary in that file to lookup the officer's name in PR.USER file.

Name is the ID  in PR.USER file.

Sample of my output

APPLIC Id.... LOAN NO.  LO_OFFICER APPLIED....   LOAN AMOUNT Sts Branch

 31786L1.2     687168          1012   15 JUN 21      1,025.00   5      6

3981L1        687182          1074   15 JUN 21        275.00   5      6

Please if anyone knows how to create this dictionary would be of great help.

Regards
Francis

------------------------------
Francis Aquila
Core Banking Support Officer
TISA
Papua New Guinea
------------------------------

Hi Francis, 

Take a look at the basic TRANS() function used into a I-Type ,

then you can define a APPLIC dictionnary like this:

ED DICT APPLIC LO_NAME

001 I
002 TRANS('PR.USER',LO_OFFICER,1,'X')
003 
004 Lo_Name from PR.USER
005 30L
006 S

​

in these exemple, I assume LO_OFFICE is the dict name of the id and the name is on the attribute 1 
then you can try LIST APPLIC LO_OFFICER LO_NAME 

I hope this hlep

------------------------------
Manu Fernandes
------------------------------

---

Hi There,

I have a retrieve sentence that displays the Loan officers number but I also want to display the officer's name.                                                                                                                   I would like to lookup officer's name in another file. Both files can be related using the operator number.

The file I am using is APPLIC
I need to create a dictionary in that file to lookup the officer's name in PR.USER file.

Name is the ID  in PR.USER file.

Sample of my output

APPLIC Id.... LOAN NO.  LO_OFFICER APPLIED....   LOAN AMOUNT Sts Branch

 31786L1.2     687168          1012   15 JUN 21      1,025.00   5      6

3981L1        687182          1074   15 JUN 21        275.00   5      6

Please if anyone knows how to create this dictionary would be of great help.

Regards
Francis

------------------------------
Francis Aquila
Core Banking Support Officer
TISA
Papua New Guinea
------------------------------

You left out some details so I had to make some assumptions. Also, there are a number of ways to do this. This is just 1 example.
The pointer below assumes the loan officer number is the key to the PR.USER file and the name is in attribute 1 of that file.

atbnoLoanOfficerNo=the attribute no. of the loan officer no. in APPLIC

DICT APPLIC LoanOfficerName

0001: I
0002: OCONV(@RECORD<atbnoLoanOfficerNo>,"TPR.USER;X;1;1")
0003:
0004:Loan Officer Name
0005:30T <--- T will word wrap or you can use L
0006:S

------------------------------
Mark Vander Veen
Project Leader, System Administrator
University of Chicago Press
------------------------------

---