in my mapping i am having mutliple files and only one target output Flat file,and i need to implement below logic.Can any one suggests me an idea ,how to do it?
field1 field2 field3
1 A B
2 C D
3 E F
4 G H
1 I J
5 K L
4 M N
6 O P
here i am reading three different files in the order File3,file2,file1 .The logic i needed is , for example if the record corresponding to '1' is present in multiple files ,then i need to write the record which is present in the first file and discard the records corresponding to 1 in the rest rest of the files.My target is a flat file and i tried with update strategy but i had later found that "update concept" wont work with flat files. So please suggest another way to get this logic
6 O P
4 G H
5 K L
1 A B
2 C D
3 E F
It would be two step design:
Create a one to one mapping loading data from files and loading into a stage table (RDBMS). While loading data into stage, be sure that the records Integration service is loading should be loaded with their corresponding file name (file name of file in which those records were present).
In INFA 8.5/8.6 we have an option that helps to extract the file name of a file of which integration service is reading records. It is there in source definition of imported file structure. Extract port gets created and we can map that port with a port (Src_FileName) that we have created in the stage table.
Structure of Stage table:
Src_fileName varchar2(50 char) -- New attribute need to add in the stage table.
So the stage data should be like below after load:
Field1 Field2 Field3 Src_FileName
1 A B File1
2 C D File1
3 E F File1
4 G H File2
1 I J File2
5 K L File2
4 M N File3
6 O P File3
Consideration for STEP 1:
- - You have rights to create & truncate the stage table.
- - You have INFA PC 8.5.x onwards version.
- Load strategy –Truncate load.
Once the stage is created:
Create a mapping with RDBMS (Stage table previously created) as the source and flat file as the target:
STAGE TABLE AS SRC,
MAX (SRC_FILENAME) RECENT_FILE_NAME
SRC.FIELD1 = RECENT_VALUE.FIELD1
SRC. SRC_FILENAME = RECENT_VALUE. SRC_FILENAME;
Consideration for STEP 2:
- - We are reading data in descending order as said in your example (file3, file2, file1). So if found in file3 then consider an instance of file3.
- - Source files are named properly, so that max/min will pick the proper (Recent file) file.
HoHope this will help....
Yes, its an interesting solution, You would probably like to check this below one as well ..
Mapping includes the below transformations in sequence -
Expression (To set Source file name file1, file2, file3) - 3 expression t/f immediately after source qualifier
Union T/F - To get all source records into one t/f (Now the data includes source filename (src_file) as part of it)
Sorter T/F - Key columns are field1, src_file
Rank T/F - Rank on src_file and Group by field1
Filter T/F - Allow records where rankindex = 1
Hope this helps ..
Good!!...by this at informatica level we can do the required thing however instead of having fixed number of source piplines (as # of files will be placed is not known in case)...it is better to read all the files by indirect listing and then do the ranking based on source filename port and grouping on field1....
so by indirect listing we will be independent of number of files coming from source and can avoid UNION operations in turn...
exactly sumit,its working finneeee..
We can implement this using dynamic lookup.. I have tried this, Working fine