0

I have a 4 flatfiles which are coming everyday,I'm taking these r flatfiles as list files as indirect file load. My requirement: I have to load these records in 4 flatfiles into the database. But the problem is I have to load only distinct records to the target and ignore the duplicate records.here I don't have any primary key ,it has 10 columns ,so all 10 columns should be considered as primary keys Could you please let me know how to load only distinct records to Target table using informatica cloud. Everyday I have to process 4 source files .I need to lookup on target and compare with the source files,and load only the distinct records.i cannot load the record which is already present in target. Please let me know your inputs. Thanks

Intiyaz
  • 9
  • 3

1 Answers1

0

Use sorter to get distinct right before target. And in the advanced tab, choose to get distinct values like pic below.

SRT Distinct

Now, if the 4 files has full load you can easily use sorter. But if they have incremental data, you need to do a lookup into the target and use a filter to exclude existing rows.

Depending on size of files, you can have perf problem but you have to increase memory size to handle it properly.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • Thanks for the help,but I'm using a list file to load 4 files at a time and also I need currently processed file name column ,when I select distinct I'm not getting distinct values because of filename. For example:my data looks like this. Col1 col2 col3 filename. A. B. C. File1. A. B. C. File2. Because of this ,I'm not getting distinct records .how to process distinct records now.please help. –  Intiyaz Aug 20 '23 at 16:47
  • If you want to do a distinct based on some columns ( exclude filename), then you need to use sorter and aggregator combination. After choosing correct columns, use an aggregator. Both sorter and aggregator should have same set of ports in order and group. – Koushik Roy Aug 21 '23 at 08:38
  • But I need filename to be shown in target table. –  Intiyaz Aug 21 '23 at 09:45
  • you will get it but it will be max(file name). So, if you have duplicate rows in file1 and file2, when it passes through aggregator, it will remove row from file1 and keep row from file2. Which row you want to keep depends on sorter order. In the end as a result you will get 1 row from file2. – Koushik Roy Aug 21 '23 at 11:21