Informatica Cloud : remove duplicate with condition

Question

I have a request to remove some rows from a source. This source contains 3 columns : Id, Type, Value, and containes some data like :

Id    Type    Value
1     Master  This is the first value
1     Second  This is a new value
1     Third   This is not a mandatory value
2     Master  Another one
2     Third   And again
3     Second  A new hope
3     Third   A third
4     Second  A single value
...

The rule to keep row is :

If single row for one Id, get the existing value

Else : If multiple rows for same Id and 'Master' exists, get the 'Master' value

If multiple rows for same Id and 'Master' not exists and 'Second' exists, get the 'Second' value

If multiple rows for same Id and 'Master' not exist and 'Second' not exists and 'Third' exists, get the 'Third' value.

In my sample so, I would like to extract only :

Id  Type    Value
1   Master  This is the first value
2   Master  Another one
3   Second  A new hope
4   Second  A single value

I try split into 3 different sources and join or lookup, but not found any parameter to discard the duplicate row.

How I can do that ?

Thanks in advance, BR Xavier

Answer 1

Try put them through a sorter to order by ID then Type and finally through an aggregater grouping by ID asc, desc depending on your requirements (lucky master comes before second which comes before third alphabetically)

Answer 2

Please find the query which can be converted in to informatica map.

create table test1
( id integer,
  type varchar2(100),
  value varchar2(1000)
);

insert into  test1 values (1,'Master','This is the first value');
insert into  test1 values (1,'Second','This is a new value');
insert into  test1 values (1,'Third','This is not a mandatory value');
insert into  test1 values (2,'Master','This is the first value');
insert into  test1 values (2,'Third','This is not a mandatory value');
insert into  test1 values (3,'Second','This is the first value');
insert into  test1 values (3,'Third','This is not a mandatory value');
insert into  test1 values (4,'Second','mandatory value');
commit;

select * from test1;    

From the below query "agg" part can be done in aggregator and decode function within aggregator transformation.

Then use joiner to join agg part and actual table

use filter to filter the required rows

  with agg as
     (select        max(case when type='Master' then 10 
                when type='Second' then 9
                when type='Third' then 8
                else 7 end) ms,

            id
      from test1
     group by id

  ) 
     select a.ms,b.* from agg a, test1 b
        where a.id=b.id
         and case when a.ms=10 then 'Master' 
                 when a.ms=9 then 'Second'
                  when a.ms=8 then 'Third'
                   else 'noval2'
                  end =b.type;