How do we eliminate duplicate records in a flat file without using Sorter and Aggregator?
Answers were Sorted based on User's Feedback
Answer / kiran
We can use dynamic cache in lookup to eliminate duplicates.
Is This Answer Correct ? | 11 Yes | 0 No |
Answer / joe
Option 1: using Unix for flat files
Option2: Using Checksum function in the expression to
generate a unique hexadecimal code for each record.
and comparing the same with the next record.
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / ankur saini
sol--seq gen---rank ---filter
add a sequence generator ...
ex input is
1 a
1 b
2 a
2 b
after seq generator
1 a 1
1 b 2
2 a 3
2 b 4
then ranl it group by all file ports rank on the seq gen key
input seq rank
1 a 1 1
1 b 2 2
2 a 3 1
2 b 4 2
add filter on rank=1
enjoy!!!!!
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / harish konda
Give the SQL query to sort the data in source in source
qualifier t/f.
And then connect to exp t/f and add one more port (say flag)
to generete numbers like, when prev row and current row
values are same, then increment number, or else give 1.
And next connect to Filter t/f and give the condition in
filter as flag=1.
Then rout the data to target.
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / isha
Select all source rows.
The Dynamic Lookup transformation builds the caches from the target table.
When the lookup evaluates a row from the source that does not exist in the lookup cache, it inserts the row into the cache and assigns the NewLookupRow output port the value of 1. When the lookup evaluates a row from the source that exists in the lookup cache, it does not insert the row into cache and assigns the NewLookupRow output port the value of 0.
The filter in this mapping checks if the row is a duplicate or not by evaluating the NewLookupRow output port from the Lookup. If the value of the port is 0, the row is filtered out, as it is a duplicate row. If the value of the port is not equal to 0, then the row is passed out to the target table.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / 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 |
How to join three sources using joiner?
What is Java Transformation? what are the uses of it?
Which tool yoU use to create and manage sessions and batches and to monitor and stop the informaticaserver?
What are the active and passive transforamtions?
How to eliminate duplicates in FF and oracle both
which one is costliest transformation? costliest means occupying more memory. which one Aggregator,sorter,filter,etc..
IN A MAPPING WHEN WE USE AGGRIGATER TRANSFORMATION WE WILL USE GROUP BY PORT. IF GROUPBY IS NOT SELECTED BY DEFAULT IT WILL TAKE ONLY THE LAST COLUMN WHY????
What is Cognos script editor?
how you will maintain version?
Please let me know how we can implement the below scenario. In a single mapping,more than 500 sources (legacy,VSAM,relational) will be loading into only one target. Whenever I retreive the data(any record) from target, i need to find the details that the record belongs to which source. Thanks in advance..
wat are deployement groups in informatica, how it will be used for developers
what is bridge tables in informatica