Dynamic Generation of Load Files based on Company Codes at Business Objects –Data Services
Aim: - To generate the load files dynamically based on the Company Codes.
Steps
1) Create a Project say PRJ_CompanyCodes at Data Services.
2) With in the Project create a Job say JOB_Codes.
3) Create a Work Flow say WF_Codes.
4) With in the Work Flow create Data Flow Say DF_Codes.
5) At DF_Codes take the source table Company Codes as source and use a Query Transform (Query 1) to extract the Distinct Company codes.
6) Now create another Query Transform (Query2) which takes the output from Query 1 and create an additional output column to get the Row counts for the specific and distinct company codes at Query2.
7) Take the output of Query 2 in a Temp Table say Table_Company.
😎 Now create another Workflow say WF_2 , and create a script which takes the below 2 parameters
i) One to initialize the distinct company code count=1 (say variable $company_code_count)
ii) Another to store the Max count of the company code.(Row Count Column from Table_Company)
9) Now create a while loop.
10) Within the while loop create a script that extracts the name of the company code based on the Row count value from Table_Company and stores the value in a variable say $CompanyCodeName.
11) Next create a Dataflow say DF_2 (with in the scope of While Loop) which takes the source as the Company code table uses a Query Transform to generate the Flat files. (Flat files should have a Customize name appended by the $CompanyCodeName).
12) Finally create a script (with in the scope of the While loop) to increment the Variable $company_code_count initialized at earlier script create at point 8.
Result:-
The Number of load files would be generated dynamically based on the distinct Company codes and will have dynamic name assigned to them based on the name of the company corresponding to the company codes.
Resultant File Names: - CustomiseName_$CompanyCodeName
($CompanyCodeName=Variable that stores the name of the company corresponding to the distinct company codes)
