How do we eliminate duplicate records in a flat file without using Sorter and Aggregator?
Answer Posted / priyank
There are several ways of achieving this. We can do it
through expression transformation and other is look up on
the target.
Expression transformation:
Create ports,
Var_PREV_KEY=Key
Var_CURR_KEY=Var_PREV_KEY
Var_CHK_DUPLICATE --> IIF(Var_CURR_KEY=Key,'DUP','NODUP')
OUT_DUPLICATE --> Var_CHK_DUPLICATE
Note: I have taken a scenario where the target table
contains only 1 Key. In case of multiple keys, will have to
create a few more Variable ports for both CURR and PREV and
in the Var_CHK_DUPLICATE port, we need to add those checks
with an 'AND' operator.E.g. For 2 keys,
Var_PREV_KEY1=Key1
Var_CURR_KEY1=Var_PREV_KEY1
Var_PREV_KEY2=Key2
Var_CURR_KEY2=Var_PREV_KEY2
Var_CHK_DUPLICATE --> IIF(Var_CURR_KEY1=Key1 AND
Var_CURR_KEY2=Key2,'DUP','NODUP')
OUT_DUPLICATE --> Var_CHK_DUPLICATE
If the Informatica version is Unix installation, then in
the pre session command you can give an unix command to
remove the duplicates from the file like
sort <file_name> | uniq > <file_name>.new
Hope it helps.
Is This Answer Correct ? | 4 Yes | 12 No |
Post New Answer View All Answers
Hi, I want to do Informatica Designer certification. Can anyone help me by providing the details on this. Thanks in advance. Regards, Arnab
Is there any way to read the ms excel datas directly into informatica?
What is the difference between a repository server and a powerhouse?
How will the document be delivered to me?
In warehouses how many schemas are there?
What happen if you have 3 ports in SQ and 4 ports in SQL override of SQ( provided all ports are in same order and they are connected with proper source and target)? Also what happens when I have 4 ports and will extract 3 values in SQloverride.. What will be the value in 4th port ?
Explain what transformation and how many are there in informatica?
What are the steps involved in the migration from older version to newer version of Informatica Server?
What are the new features of informatica 9.x in developer level?
Can you use flat files in Mapplets.
can u any challenge your project?
What are the types of lookup transformation?
Tell me any other tools for scheduling purpose other than workflow manager pmcmd?
Describe the scenarios where we go for joiner transformation instead of source qualifier transformation?
What is dimensional table? Explain the different dimensions.