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
Please let me know how to make Data masking in informatica..
What will happen if the select list columns in the custom override sql query and the output ports order in sq transformation do not match?
What is the difference between informatics 7x and 8x and what is latest version?
how to load dimension table and fact table. plz tell me the steps.
What is a mapplet/worklet in informatica?
permutations of mapplet
What is the difference between STOP and ABORT options in Workflow Monitor?
How are the sources and targets definitions imported in informatica designer?
Hi, I saw one mapping implemented by my seniors . In Expression transformation they implemented following logic. That is iif(is_date(in_UC_DATINV,'YYYYMMDD'),to_date(in_UC_DATINV,'Y YYYMMDD'),'Inventory Date is either invalid or null') Inventory_Date is validated only for is_date() But not validated for notisnull() . But error says “ either invalid or null “ why? Whether is_date() also check for not isnull() ? or in this logic something is different ? Please answer me . Advance thanks
What are the components of the workflow manager?
How can you define user defied event?
Tell me about MD5 functions in informatica
How to implement security measures using repository manager?
Hi friends I want to know about what r the dimensions in the banking porjects and genarally how many tables r in project ?
What is meant by active and passive transformation?