ODI create the run time CSV/XLS file based on distinct value in the source Data.

User_HG7TT

Source is department table, and department number get populated by some other system.

ODI read the department table, find the distinct department number from column on run time.

Create the separate files based on how many department no distinct values in the table.

If the table has 3 distinct value, it will create the 3 separate file.

If the table has 4 distinct value, it will create the 4 separate file.

Output file name we can add the distinct value in the name of the file.

For example if I have 10, 20, 30 department table, I have to create the 3 output files from the source table using variable. In future department number might be added and file should get created automatically. Could you please advise which module I need to look to address this requirement? Or if you can send me the documentation steps that would be really useful.

Thanks a lot.

Christyxo

You will need to build a package that loops through the distinct values.

First you want to create a variable v_DEPT_RANK against any logical schema. This is used to increment through the loop. The query here is redundant as the package will control what the value actually is, so just write

SELECT 
  1 
FROM
  DUAL 

Then create a variable v_DEPT_ID that points to your table and uses the above variable

SELECT
  DEPT_ID
FROM
  (
    SELECT DISTINCT
      DEPT_ID
    , DENSE_RANK ( ) OVER ( ORDER BY DEPT_ID ) RNK
    FROM 
      TABLE
  )
WHERE RNK = #v_DEPT_RANK

Open the file in the design model and change the resource field to #v_DEPT_ID

Create a mapping and filter the department id using v_DEPT_ID to populate your file. When your file gets created it should be created with the file name matching the department ID.

Then you bring everything into a package.

• First step would be SET VARIABLE v_DEPT_RANK = 1
• Second step would be REFRESH VARIABLE v_DEPT_ID
• Third step would be your mapping
• Fourth step would be INCREMENT VARIABLE v_DEPT_RANK +1

Then the 4th step would point back to the second step thus completing your loop.

This will fail when you have completed all distinct values in your loop so you also want to include a step that handles STEP 2 failing. Just point it to a beep, or a sleep component.

Once you are familiar with this approach there are multiple approaches that you can adopt to clean up the logic but this will be the basic start point. It will look something like this: