Performing a lookup on a flatfile isn’t a bid deal in DI. Here is the way to go, follow the steps.
- Here, EMP (Table) is my source and DEPT (Flatfile) is my translate table. Based on the deptno i want to get Dept. Name and location
- Place EMP Table as a source, and in the next step dragged a QRY transform from the transform object library, and then target table object on to the workspace.
- Now, in the QRY Transform(Flatfile_lookup), drag the required columns from the In schema to Out schema.
- Give a right click, select the New function call, select the Lookup Functions and select lookup_ext and the click on next button.
- A lookup_ext wizard window will be opened. Now, click on Translate Table browse button. Input prameter window will be opened
- Select the File Format in the look in browse button, choose your file definition (here it is DEPT) and click on OK.
- Here you need to provide full file path, click on the Browse button to locate your file and then click on OK.
- Now, specify your condition as DEPTNO = EMP.DEPTNO and then select DNAME, LOCin the ouput parameters list and click on Finish button
- That’s it you’re done with your design part. Save the job, execute it and check the data.